user1854438 February 2016

Decode does not pick up first character

Why does this fail for the first character in oracle sql?

select DECODE( TRANSLATE('1','123',' '), NULL, 'number','contains char') from dual

This works because 1 is the second digit

select DECODE( TRANSLATE('1','4123',' '), NULL, 'number','contains char') from dual

But this fails because 4 is the first digit

select DECODE( TRANSLATE('4','423',' '), NULL, 'number','contains char') from dual


algor February 2016

First let's take a look at translate function definition:

TRANSLATE(expr, from_string, to_string): TRANSLATE returns expr with all
occurrences of each character in from_string replaced by its corresponding 
character in to_string. Characters in expr that are not in from_string are not replaced. 
If expr is a character string, then you must enclose it in single quotation marks. 
The argument from_string can contain more characters than to_string. In this case, 
the extra characters at the end of from_string have no corresponding characters 
in to_string. If these extra characters appear in char, then they are removed 
from the return value.

i.e. TRANSLATE(some_string,'123','abc'): 1 will be replaced by a, 2 by b, 3 by c(I will use arrow -> instead of "replaced by" further)

Now let's take a look at our examples:

TRANSLATE('1','123',' '):  1 -> " ", 2->nothing, 3->nothing. 
(nothing means removed from the return value, see definition) 

Result of above function is string consisted of whitespace - " "

TRANSLATE('1','4123',' '): 4 -> " ", 1->nothing, 2->nothing, 3->nothing

Result of the above function is empty string "". Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null.

TRANSLATE('4','423',' '): 4->" ", 2->nothing, 3->nothing

Result of the above function is whitespace string as in the first example.

That is why you are getting "contains char" in the first and third queries, and number in the second one

Post Status

Asked in February 2016
Viewed 2,716 times
Voted 12
Answered 1 times


Leave an answer