Friday, September 05, 2014

Regular Expressions in SQL Server, DB2 UDB, and Oracle

I was a bit surprised that not all RDBMS platforms offer robust, native support for regular expressions.

Oracle:

Oracle introduced this in 10g. It is implemented in roughly the way I would expect and looks like it's pretty useful.
Reference

DB2:

DB2 seems to have this capability in 9.7. NOTE that I have not checked to see if it was available in earlier releases.  However, it appears you have to go through the XQuery interface. I'm not surprised by this, but a bit disappointed.  I had hoped for an implementation more like Oracle's. I think that the DB2 way is going to be cumbersome for the average user.

Example:
db2 "with val as (
 select t.text
 from texts t
 where xmlcast(xmlquery('fn:matches(\$TEXT,''^[A-Za-z 0-9]*$'')') as integer) = 0
)
select * from val"

Reference1, IBM DB2 for LUW 9.7 information center reference

MS SQL Server: 

SQL Server, it seems, requires you to roll your own solution or look to some sort of 3rd party.  I'm very surprised by this.

There are 3rd party libraries that appear to be pretty robust.  One example can be found here: SQL Server Central article on RegEx in MSSQL Server

I suppose that they want us to use the full text search, which appears to have robust features, and address the same purpose.  Still, just a bit surprised ...

No comments:

I had previous written very breifly about  creating a report on disk space using WMI A much better, and very concise instruction for qu...