Experiment with this:
SELECT MyCol, translate( xor(MyCol, translate(MyCol, x'00000000000000000000', '0123456789')), ' ', x'00') FROM MyTable
Assuming that MyTable has a column named MyCol and that MyCol is a character column that has at least one numeric digit but mixed with non-numeric characters, the result column should contain just the numeric digits.
Now, there may be leading, trailing or embedded blanks in the result. It's not clear what you are actually trying to achieve. Perhaps after viewing results, you can provide more examples that can narrow things down.
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.