Clean phone number field with IBM DB2 for i SQL REGEXP_REPLACE

Say you have a phone number in various formats but you just want the numeric characters and all the non-numeric characters removed. Well use the SQL function REGEXP_REPLACE and look for characters with the regular expression [^\d] and replace it with empty character ”. [^\d] is a negated set which means match any characters not in between [^ and ]. \d is a short hand way of saying all digit characters 0-9.

SELECT PHONE_NUMBER, REGEXP_REPLACE(PHONE_NUMBER,'[^\d]’,”)
FROM MYTABLE;

123-123-1234,1231231234

References:
IBM documentation on REGEXP_REPLACE
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzscaregexp_replace.htm

Online regex tester tool
http://regexr.com/

Advertisements

1 thought on “Clean phone number field with IBM DB2 for i SQL REGEXP_REPLACE

  1. alanseiden

    Good example of how to use REGEXP_REPLACE, Dave. I think I’ll find opportunities to use REGEXP_REPLACE inside sn SQL view, to provide data to application developers in the format they need. Thanks for writing about these new DB2 capabilities.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s