Topic
1 reply Latest Post - ‏2012-08-08T13:46:06Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic Replace non standard characters with blanks

‏2012-08-08T11:31:51Z |
I need to replace non standard characters in a string with blanks.

I can easily find them using translate:
SELECT cmname
translate(upper(cmname),
' ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890')
FROM acorc/cacmst
WHERE translate(upper(cmname),
' ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890') > ' '
But I can't seem to hit on the correct combination of functions (translate,replace,position)
to find a way to blank anything in the string that is not included in the list of standard characters (ABC...)

I need to turn '$ABC%DEF*' into ' ABC DEF '

Can anyone help?

Thanks!
Updated on 2012-08-08T13:46:06Z at 2012-08-08T13:46:06Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: Replace non standard characters with blanks

    ‏2012-08-08T13:46:06Z  in response to SystemAdmin
    Found my answer here:
    http://ibmmainframes.com/about43712.html

    Thanks lkhiger!