What type of format do you want it in? You might be able to use the strip, replace:
SELECT REPLACE(clphone, ‘/’, ‘-‘) (field, character being replaced, replace with)
You would need to do several of these depending on the characters being changed. If there is a blank and you know the position you could add the substring feature.
[kccrosser] Builder400 – you are on the right track with the use of a function. There is a Replace function built-in, which is fine for replacing *known* characters. However, if your source data contains characters for which you didn’t code, then the output data will still have the non-numeric characters.
Instead, create a simple function that takes a string and returns a string, like (VB pseudocode – I don’t work on AS/400 systems):
<pre>function StripChars ( sInstring as string ) as string
i as Integer
sOutString as string
sOutString = “”
for i = 1 to len(sInString)
if substring(sInString, i, 1) in (‘0′,’1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9’) then
sOutString = sOutString & substring(sInString, i, 1)
next ‘ i
StripChars = sOutString
Basically, instead of trying to remove the non-numerics, the easy approach is to detect and keep the numerics.