<< Lotusphere schedule... | Home | Notes 5, 6.5 and 7 compability issue? >>

Using regex in SELECT statement

Working with the new version of the MySQL database I saw that it supports regular expressions in the WHERE clause of SELECT statements out of the box. This is totally cool and has the posibility of seriously simplifying complex WHERE statements. Previously I have opted to a simpler condition and then filter the rest in the application, but having regular expressions in the database allows me to keep the filtering as close to the data as possible. Cool.

Since we are an IBM shop at the office we run DB2 internally so I searched for a way to do this in DB2. It isn't natively available but so is the next best thing - as a UDF (user defined function). I even found an article on developerWorks describing how to do it using a UDF in the C-programming language: Bringing the Power of Regular Expression Matching to SQL.

I haven't tried yet but it looks very promising though I might consider doing the UDF in Java which will be easier, at least for me, being more comfortable with Java than C. Doing it in the C programming language you would need a C-compiler though I think you would do it using cygwin instead of Microsoft Visual Studio.