Regular Expressions in Db2 SQL

Regular Expressions in DB2 SQL

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.

In DB2 SQL RegEx, how can a conditional replacement be done without CASE WHEN END..?

Try this (replace "digits not followed by X_or_digit"):

with t(s) as (values
  '12X125'
, '6X350'
, '1X1500'
, '1500'
, '1125'
)
select regexp_replace(s, '^([\d]+(?![X\d]))', '1X\1')
from t;

How to split a string in db2?

I would recommend to take a look at REGEXP_SUBSTR. It allows to apply a regular expression. Db2 has string processing functions, but the regex function may be the easiest solution. See SO question on regex and URI parts for different ways of writing the expression. The following would return the last slash, filename and the extension:

SELECT REGEXP_SUBSTR('http://fobar.com/one/two/abc.pdf','\/(\w)*.pdf' ,1,1) 
   FROM sysibm.sysdummy1

/abc.pdf

The following uses REPLACE and the pattern is from this SO question with the pdf file extension added. It splits the string in three groups: everything up to the last slash, then the file name, then the ".pdf". The '$1' returns the group 1 (groups start with 0). Group 2 would be the ".pdf".

SELECT REGEXP_REPLACE('http://fobar.com/one/two/abc.pdf','(?:.+\/)(.+)(.pdf)','$1' ,1,1) 
   FROM sysibm.sysdummy1

abc

You could apply LENGTH and SUBSTR to extract the relevant part or try to build that into the regex.

---

Related Topics