ASQL statement for searching SSN number in a table that can have up to 4800 VARCHAR

Question

I am searching for SSN number that was indicated in a Table with 4800 varchar.

I tried

SSN_MSG Like ‘%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%’

But it didn’t work. I need to check for the format of SSN which is SSN 000-00-0000

Then why did you tag for MySQL? What version of DB2 are you using? — Tim Biegeleisen, Mar 14 '19 at 00:12
That Is what i expected but as i tried it i received a message “No rows qualified”. But In my test table i did put SSN number such as “SSN should be123-45-6789” — Rey Santos, Mar 14 '19 at 00:16
Sorry this is my first time asking question in stackoverflow. — Rey Santos, Mar 14 '19 at 00:17

score 0 · Answer 1 · answered Mar 14 '19 at 01:25

0

From what I have read, DB2 version 11 should support REGEXP_LIKE:

WHERE REGEXP_LIKE(SSN_MSG, '[0-9]{3}-[0-9]{2}-[0-9]{4}')

answered Mar 14 '19 at 01:25

Tim Biegeleisen

502,043
27
286
360

Db2 for LUW and DB2 for Z/OS are different products. [REGEXP_LIKE](https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_bif_regexplike.html) function you mentioned is supported starting from V12 there. – Mark Barinstein Mar 14 '19 at 03:53
@MarkBarinstein Well in this case maybe the OP would have to resort to using a user defined function. – Tim Biegeleisen Mar 14 '19 at 04:09

score 0 · Answer 2 · answered Mar 14 '19 at 06:54

For earlier DB2 versions the fn:matches function can be used, which allows the same regular expression.

with tab (str) as (
select '123-45-6789' from sysibm.sysdummy1
  union all
select '123456789' from sysibm.sysdummy1
)
select str
from tab
where xmlcast(xmlquery('fn:matches($s, "[0-9]{3}-[0-9]{2}-[0-9]{4}")' passing str as "s") as int)=1;

STR
-----------
123-45-6789

ASQL statement for searching SSN number in a table that can have up to 4800 VARCHAR

2 Answers2