CODEWITHSUNDEEP
sqlregexdb2
Samer Buna
Samer Buna

Reputation: 9311

Regular Expressions in DB2 SQL

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

Upvotes: 13

Views: 53390

Answers (5)

data_henrik
data_henrik

Reputation: 17118

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

Upvotes: 9

tessus
tessus

Reputation: 148

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.

Upvotes: 4

AidanH
AidanH

Reputation: 51

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

Upvotes: 5

AngocA
AngocA

Reputation: 7693

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:

Upvotes: 6

komikoni
komikoni

Reputation: 71

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.

Upvotes: 7

Related Questions