Remove numbers from a character value - scalar functions

Thursday, August 13, 2009

One developer asked me to help out his project. He wanted to remove the numbers,any special chars in his field values,

eg., '99kirkh-ammet99' to 'kirkhammet' and ' db2' to 'db'

I tried using translate, but didn't know what to do with the space that came in due to the function. Got help from one friend for the same.
Here is the final one : easy for experts, useful for newbies.

VALUES REPLACE(TRANSLATE('999kirk-hammet9 8', '', TRANSLATE('999kirk-hammet9 8',
'#', 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '#')), ' ', '') ;

To separate the command into pieces to explain it,

1. the inner translate :
TRANSLATE('999kirk-hammet9 8','#','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '#')

- converts any characters to #, and ref. the syntax of translate function (new), pads # if final string is smaller than initial. Please read the new syntax. Very useful function.

Result :
'####-######9 8'
2. Outer translate:
This converts all the values (in our example - #, -, 9, 8) to spaces in the value.

Result :
' kirk hammet '

3. Replace :
This replaces the spaces with empty places thus getting us - 'kirkhammet'

If anyone got useful functions or links for the same, please comment.