Oracle SQL Function: TRANSLATE

Long time back i had looked at this function and thought what a useless function. As always i was wrong. Few days back i used this function to check if a given string is numeric or non numeric. This function turned out to be a great help.

Following code can check if ORIG_SYSTEM_REFERENCE column of HZ_PARTIES table is numeric or non-numeric.

SELECT orig_system_reference , DECODE(translate(orig_system_reference,'X1234567890','X'),NULL,'NUMERIC' ,'NON-NUMERIC' ) FROM ar.hz_parties ;

In the above example, any digit from 0 to 9 will be replaced by NULL, 'X' will be replaced by 'X' and any other chacter will not be transformed or translated. I had to use 'X' in second argument to translate function because if third argument is NULL then translate does not work, so i had to use some character in third argument and decided to use 'X'.