Function to extract characters from a column value?

[sgursahaney]

I need a DB2 function that removes the alphabetic characters from a column value. For example, I have a PHONE_NUMBER column that is supposed to have only numeric characters. But over the years, users have been putting additional characters in this field (e.g. "(800) 555-1212" or "TEL 800-555-1212&quot. What I would like to do is only retrieve the numeric portion of the column value (e.g. "8005551212&quot.

I have a function that I got to work, but I have to use it in several places and it is making my SQL statement excessively long. Does anybody know a simple function that can return to me only the numeric values from a character field?

 

[alphonsus]

Hi,

did you take a look at the Sql REPLACE function?


Good luck,
Alphonsus.

 

[sgursahaney]

Yes, unfortunately REPLACE matches an entire string rather than individual characters. I was able to solve my problem using the TRANSLATE function, but I find the solution clumsy. Here is what I came up with:

TRANSLATE(UCASE(CR_DEBT_PHONE),'',' -()ABCDEFGHIJKLMNOPQRSTUVWXYZ.')

This returns exactly what I am looking for, but I was hoping someone might have a more elegant solution.