Regular Expressions in DB2 SQL

(Other than using a UDF) Any R开发者_Go百科EGEXP-In-SQL support for DB2 9.7 ?

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

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.

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

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

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.