1

I want to use something like REGEXP_SUBSTR in DB2 (version 10.5).

There is an example of what I tried:

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

I got this error : [Error Code: -420, SQL State: 22018]

09:23:12  [SELECT - 0 row(s), 0.000 secs]  [Error Code: -420, SQL State: 22018]  DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=INTEGER, DRIVER=3.57.82
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]
Goldray
  • 61
  • 1
  • 13
  • Do you want to use any regular expression or just search for string snippets? DB2 does not have regex_substr. Your options depend on what you want to do. – data_henrik Sep 16 '15 at 10:10
  • 1- I want to test if a column contains the regular expression, than extract it to be used by another column. Example, update table_name set column_name = REGEXP_SUBSTR(column2_name,'^[A-Za-z0-9]*$') where condition; – Goldray Sep 16 '15 at 10:32
  • Look at this Stackoverflow question on how to emulate it http://stackoverflow.com/questions/4763757/regular-expressions-in-db2-sql – data_henrik Sep 16 '15 at 10:45
  • 1
    REGEXP_SUBSTR does not exist in DB2! The way to use regular expressions is with xquery (I already answered that question). What you can do is to wrap the xquery into a UDF in order to have the function you said. Also, you can install a set of stored procedure that provide regular expressions (look in DeveloperWorks) – AngocA Sep 16 '15 at 13:47

2 Answers2

0

There is no equivalent function to REGEXP_SUBSTR in DB2.

However you can achieve similar results with the XMLQUERY function

SELECT 
  XMLCAST(
   XMLQUERY('fn:replace($src,"^hello | you$","")' 
   PASSING 'hello to you' AS "src")
  AS VARCHAR(255))
FROM SYSIBM.SYSDUMMY1;

Difficulty Here, fn:replace removes matched patterns because DB2's implementation doesn't support () and $1 sub-grouping patterns

Stavr00
  • 3,219
  • 1
  • 16
  • 28
0

With DB2 V11.1 there is now REGEXP_SUBSTR(). It works simply as this:

Example 1

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

Return the string which matches any character preceding a 'o'.

The result is 'lo'.

Example 2

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

Return the second string occurrence which matches any character preceding a 'o'.

The result is 'to'.

Example 3

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

Return the third string occurrence which matches any character preceding a 'o'.

The result is 'yo'.

Community
  • 1
  • 1
SovietFrontier
  • 2,047
  • 1
  • 15
  • 33