7

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?

^a[aofdmep]{1}[a-z]{1}[a-z0-9]{4}[sidbfkfpo]{1}

Edit

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

WHERE USER_NAME NOT LIKE 'a_______'

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 – vettipayyan Jan 17 '11 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 '11 at 19:47
11

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

^[aofdmep][a-z][a-z0-9]{4}a[sidbfkfpo]

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.

  • +1. Good option, thanks a lot! I'll a bit wait for other answer if any. – tmow Jan 17 '11 at 14:54
  • Finally, after over 2 years, I've decided that your answer is the most correct and clean. Thx! – tmow Feb 11 '13 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 '17 at 13:17
  • yep, true, but still the correct answer. – tmow Nov 19 '17 at 21:50
11

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
where
   xmlcast(
     xmlquery('fn:matches($USER_NAME,"^a[aofdmep][a-z][a-z0-9]{4}[sidbfkfpo]")') 
     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. – Lonnie Best Apr 20 '17 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 '17 at 22:19
  • Here's my question. – Lonnie Best Apr 21 '17 at 14:54
6
SELECT * FROM
   (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 '11 at 7:27
  • I'm checking if it works on DB2 and how to modify it eventually – tmow Jan 18 '11 at 7:51
  • 1
    The DUAL table doesn't exist in DB2, but it's possible to use the SYSIBM.SYSDUMMY1. – tmow Jan 18 '11 at 8:45
2

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.

SELECT *
  FROM (SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1) T
 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.

SELECT *
  FROM (SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1) T
  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.

2

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

0

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

select whatever
from users
where
   xmlcast(
     xmlquery('fn:matches($USER_NAME,"^a[aofdmep][a-z][a-z0-9]{4}[sidbfkfpo]")'
      PASSING USER as "USER_NAME") 
     as integer) = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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