Regular Expressions in DB2 SQL

(Other than using a UDF) Any REGEXP-In-SQL support for DB2 9.7 ?

Solution 1:

Starting with DB2 11.1 there is built-in regex support. One of the new function is REGEXP_SUBSTR and there are some more.

SELECT REGEXP_SUBSTR('hello to you', '.o',1,1) 
   FROM sysibm.sysdummy1

Solution 2:

I'm komikoni(Keisuke Konishi).

I created the regular expression function (UDF) which does not exist in db2. The UDF using the SQL/XML(Xquery). You can easily install.

List of regular expressions provide UDF

1. REG_MATCHES provides Coincidence existence ( Scalar )
2. REG_REPLACE string substitution ( Scalar )
3. REG_COUNT number of matches retrieved ( Scalar )
4. REG_POSITION match position acquisition ( Scalar )
5. REG_SUBSTR gets a string matching ( Scalar )
6. REG_SUBSTR_TABLE list of matching string information ( Table )
7. REG_TOKENIZE_TABLE list of mismatched string information (divided by a separator string) ( Table )
8. REG_ALLTOKEN_TABLE list of mismatch string and matching string information ( Table )

Scripts can be downloaded from here. (Sorry in Japanese)

https://www.ibm.com/developerworks/jp/data/library/db2/j_d-regularexpression/

(English : Machine translation Script : The last of a Japanese page)

I look forward to your feedback and comments.

Solution 3:

The real answer is that DB2 does support regular expression since PureXML was added (v9.7 included) via xQuery with the matches function.

For 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"

For more information:

• http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/xqrfnmat.html

Solution 4:

That works fine except for DB2 z/OS - in DB2 v10 z/OS you must use PASSING as follows

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

Solution 5:

There is no built-in support for regular expressions in DB2 9.7.

The only way is using UDFs or table functions as described in the article 'OMG Ponies' added in the comment.

@dan1111: I do not appreciate my post being edited, especially if people can't read the question correctly. The OP asked Any REGEXP-In-SQL support for DB2 9.7

SQL is not XQuery !!!

Sorry, don't delete the text of my 100% correct answer. You can add a comment or write your own answer.