Extract complex text pattern using SQL function

Question

I need to extract a text pattern looking like this:

NN-NNN-NNNNNNNNN 
(2digit,minus, 3digits,minus,9digits)

from along text field. For example, specific field looks like this:

"fsadlk fasldkl jhkjfd khjfasd 11-333-878787618 hfsd uhjkfads"

I need to extract using sql function in the select line the 11-333-878787618. I'm using DB2 AS400 but any SQL function syntax will help.

Please tag your question with the database you are using. Also, edit the question and provide sample data and desired results. — Gordon Linoff, Aug 05 '16 at 11:29

score 1 · Answer 1 · answered Aug 05 '16 at 13:27

1

There is no built-in SQL function that provide you with what you are trying to accomplish. I would suggest you have a look at this. It's an IBM post about how to do Regular Expressions on the iSeries DB2 SQL using User Defined Functions. And then the regex pattern to match the text would be:

\d\d-\d\d\d-\d\d\d\d\d\d\d\d\d

It would be nice to use the proper Regular Expression pattern of:

\d{2}-\d{3}-\d{9}

But this regex function that IBM implemented is rather limited.

answered Aug 05 '16 at 13:27

Adrian Bannister

523
4
11

1

According to the documentation here https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzregexp_like.htm the {n} modifiers should work at least at v7.1 and later. The real caveat is that the International Components for Unicode must be installed. I don't have those installed here, so I can't test this. The DB2 function you are looking for is `REGEXP_LIKE`. – jmarkmurphy Aug 05 '16 at 15:23
BTW if you are looking for ICU for IBM i, it is option 39 of the operating system. – jmarkmurphy Aug 05 '16 at 15:35
Thanks very much! the regex is very powerful though i couldn't run it on my iSerious SQL environment – Yossi Kelberman Aug 05 '16 at 20:37

score 1 · Accepted Answer · answered Aug 05 '16 at 15:50

The following may be useful in that regard; I can add the extraction as well, but I figured that might be understood already, after getting this far:

 create table patterns ( p varchar(65), find_at smallint )
 ;
 insert into  patterns values                                        
  ('fsadlk fasldkl jhkjfd khjfasd 11-333-878787618 hfsd uhjkfads',31)
 ,('weroiu 11-333-8787zz618 oiuwens snkdl osjlwhe'               ,00)
 ,('nm,sdj xhdgweufyen sndh 711-333-878787618 hfsd uhjkfads'     ,26)
 ,('nm,sdj xhdgweufyen sndh 11-333-8787876187 hfsd uhjkfads'     ,25)
 -- ....+....1....+....2....+....3....+....4....+....5....+....6.    
 ;
 select translate(p, '9', '0123456789', '9') as translated
     ,  smallint( locate( '99-999-999999999'              
                , translate(p, '9', '0123456789', '9') )  
                ) as located                              
     ,  find_at                                           
 from patterns                                            
 ; -- report from the above query follows:
TRANSLATED                                                         LOCATED   FIND_AT
fsadlk fasldkl jhkjfd khjfasd 99-999-999999999 hfsd uhjkfads            31        31
weroiu 99-999-9999zz999 oiuwens snkdl osjlwhe                            0         0
nm,sdj xhdgweufyen sndh 999-999-999999999 hfsd uhjkfads                 26        26
nm,sdj xhdgweufyen sndh 99-999-9999999999 hfsd uhjkfads                 25        25

Extract complex text pattern using SQL function

2 Answers2