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

2 Answers 2

up vote 6 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

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.