Playing with regular expressions and DB2 for i


We talked about regular expressions and DB2 for i some time ago … (look at SQL DB2 for i and Regular Expression) and today I stumbled upon this post … Clean phone number field with IBM DB2 for i SQL REGEXP_REPLACE.

Let’s we try to clean a phone number with a simple regular expression:

WITH exampletable AS                                             
 (SELECT '02/123 45678' AS phonenbr FROM sysibm/sysdummy1)       
SELECT phonenbr AS original, REGEXP_REPLACE(PHONENBR,'[^\d]','') 
 AS cleaned                                                      
 FROM exampletable

And this what we get:

02/123 45678 0212345678

I think it’s time to study regular expressions to manage strings in a simple way !

Let’s we start with those guides: