Oracle PL/SQL to check whether variable is text or number

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'