Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

4 Answers 4

up vote 7 down vote accepted

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.

share|improve this answer
    
+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
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');
share|improve this answer
    
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
    
The DUAL table doesn't exist in DB2, but it's possible to use the SYSIBM.SYSDUMMY1. –  tmow Jan 18 '11 at 8:45

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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