I posted the same on stackoverflow (please, let me know if I have to delete one).

I'm working on a DB2 database and as far as I can see regexp is not supported (without additional libraries).

So I cannot implement something similar to what is explained in this article "Bringing the Power of Regular Expression Matching to SQL"

Do you know if I can "emulate", with a SQL statement, a regular expression like this?



In the above hypothesis I found that it's acceptable for my case a WHERE Like predicate:


But it's a unsafe and doesn't cover other cases where I don't have a fixed char that I can match.

  • You might want to look at this article : Thoughts on Regular Expressions Jan 17, 2011 at 15:58
  • 1
    So this may actually be in the class of answer, but I'm not sure :: In SqlServer we would do this by way of a CLR regex component, and the DB2 engine seems to allow for the use of Java in the same way. Why can't you farm this out to a properly written, very lean (likely to be used in a tight loop scenario) Java jar?
    – jcolebrand
    Jan 17, 2011 at 19:47

6 Answers 6


First off, the {1}s are redundant, so it's really just:


This is actually a pretty simple patterh ... it's just looking at what the first 8 characters of the string are, and it's always a fixed length string, so you could possibly build a table of all of the permutations, and then do:

WHERE SUBSTR(string_to_match, 1, 8) IN (
  SELECT valid_prefixes FROM table_of_things_to_match

Unfortunately, it's 7*26*36^4*9 ... 2.75 billion possible combinations, but, still, fixed strings, so you could do:

WHERE SUBSTR(string_to_match, 1, 1) IN ('a','o','f','d','m','e','p')
  AND SUBSTR(string_to_match, 2, 1) IN ('a','b','c','d' ... 'z')
  AND SUBSTR(string_to_match, 3, 1) IN ('a','b','c','d' ... 'z','0','1' ... '9')
  AND SUBSTR(string_to_match, 4, 1) IN ('a','b','c','d' ... 'z','0','1' ... '9')
  AND SUBSTR(string_to_match, 5, 1) IN ('a','b','c','d' ... 'z','0','1' ... '9')
  AND SUBSTR(string_to_match, 6, 1) IN ('a','b','c','d' ... 'z','0','1' ... '9')
  AND SUBSTR(string_to_match, 7, 1) = 'a'
  AND SUBSTR(string_to_match, 8, 1) IN ('s','i','d','b','f','k','p','o')

(fill in the ... bits, of course)

oops, there's two fs in that last character class, so there's only 2.45 billion permutations.

I'm not going to pretend that it's going to be fast ... it likely won't be, but it'll get you the pattern you're looking for. If you tend to do this a lot, I'd likely build a table of characters so you had an easy way to select out alpha / numeric or alpha-numeric.

  • Finally, after over 2 years, I've decided that your answer is the most correct and clean. Thx!
    – tmow
    Feb 11, 2013 at 14:00
  • 1
    After looking at this 6+ years later because it got an upvote ... how the hell did I end up moving the a moved from position 1 to position 7 when I removed the {1}s?
    – Joe
    Sep 15, 2017 at 13:17
  • yep, true, but still the correct answer.
    – tmow
    Nov 19, 2017 at 21:50

Since this old question has been dug up anyway, I'll mention that you can use the built-in XQuery support in DB2 for regular expression matching, something along the lines of

select whatever
from users
     as integer) = 1

XMLQUERY above calls the XQuery matches function against the column USER_NAME. The result is an XML boolean, so the XMLCAST is used to convert it to an SQL data type.

  • I'm trying to put a column name where you have $USER_NAME, but keep get thing this error. Please comment. Apr 20, 2017 at 20:17
  • @LonnieBest please ask a separate question. Be sure to provide sufficient details, not just a picture of an error message.
    – mustaccio
    Apr 20, 2017 at 22:19
  • Here's my question. Apr 21, 2017 at 14:54
   (SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1)
WHERE substr(MyString,1,1) = 'a' 
AND   substr(MyString,2,1) IN ('a','o','f','d','m','e','p')
AND   substr(MyString,3,1) BETWEEN 'a' AND 'z'
AND  (substr(MyString,4,1) BETWEEN 'a' AND 'z' 
   OR substr(MyString,4,1) BETWEEN '0' AND '9')
AND  (substr(MyString,5,1) BETWEEN 'a' AND 'z' 
   OR substr(MyString,5,1) BETWEEN '0' AND '9')
AND  (substr(MyString,6,1) BETWEEN 'a' AND 'z' 
   OR substr(MyString,6,1) BETWEEN '0' AND '9')
AND  (substr(MyString,7,1) BETWEEN 'a' AND 'z' 
   OR substr(MyString,7,1) BETWEEN '0' AND '9')
AND   substr(MyString,8,1) IN ('s','i','d','b','f','k','p','o');
  • Thx a lot! Brilliant as Joe solution but easier.
    – tmow
    Jan 18, 2011 at 7:27
  • I'm checking if it works on DB2 and how to modify it eventually
    – tmow
    Jan 18, 2011 at 7:51
  • 1
    The DUAL table doesn't exist in DB2, but it's possible to use the SYSIBM.SYSDUMMY1.
    – tmow
    Jan 18, 2011 at 8:45

Building on Leigh Riffel and Joe's answers, you might consider using LIKE when you have a long list of individual characters, or when you have multiple character ranges.

 WHERE substr(MyString,1,1) = 'a'
   AND 'aofdmep' like '%'||substr(MyString,2,1)||'%'
   AND substr(MyString,3,1) BETWEEN 'a' AND 'z'
   AND 'abcdefghijklmnopqrstuvwxyz0123456789' like '%'||substr(MyString,4,1)||'%'
   AND 'abcdefghijklmnopqrstuvwxyz0123456789' like '%'||substr(MyString,5,1)||'%'
   AND 'abcdefghijklmnopqrstuvwxyz0123456789' like '%'||substr(MyString,6,1)||'%'
   AND 'abcdefghijklmnopqrstuvwxyz0123456789' like '%'||substr(MyString,7,1)||'%'
   AND 'sidbfkpo' like '%'||substr(MyString,8,1)||'%'

Since you use the same list of characters multiple times, you might also consider using a CROSS JOINed constant column.

  CROSS JOIN (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' alphanum FROM SYSIBM.SYSDUMMY1) T2
 WHERE substr(MyString,1,1) = 'a'
   AND 'aofdmep' like '%'||substr(MyString,2,1)||'%'
   AND substr(MyString,3,1) BETWEEN 'a' AND 'z'
   AND alphanum like '%'||substr(MyString,4,1)||'%'
   AND alphanum like '%'||substr(MyString,5,1)||'%'
   AND alphanum like '%'||substr(MyString,6,1)||'%'
   AND alphanum like '%'||substr(MyString,7,1)||'%'
   AND 'sidbfkpo' like '%'||substr(MyString,8,1)||'%'

Not needed for your example, but the CROSS JOINed "table" could define multiple named character class columns.


REGEXP_LIKE is now available in DB2 for iSeries - see: http://www.itjungle.com/fhg/fhg051915-story01.html


In DB2 for z/OS the SQL would include PASSING as follows

select whatever
from users
     as integer) = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.