Issue : sometime its very important to check a variable is number or text in order to avoid any exception.
Assume your variable is xyz
SELECT DECODE(TRIM(TRANSLATE(xyz,’0123456789′,’ ‘)), NULL, ‘number’,’characters’) FROM DUAL;
SELECT DECODE(TRIM(TRANSLATE(’12s3456′,’0123456789′,’ ‘)), NULL, ‘number’,’characters’) FROM DUAL;
Returns : characters
SELECT DECODE(TRIM(TRANSLATE(‘1233456′,’0123456789′,’ ‘)), NULL, ‘number’,’characters’) FROM DUAL;
Returns : number
What is Translate Function:
For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.
TRANSLATE('1tech23', '123', '456')
Result: '4tech56'
TRANSLATE('222tech', '2ec', '3it')
Result: '333tith'