sql - Decode does not pick up first character -


why fail first character in oracle sql?

select decode( translate('1','123',' '), null, 'number','contains char') dual 

this works because 1 second digit

select decode( translate('1','4123',' '), null, 'number','contains char') dual 

but fails because 4 first digit

select decode( translate('4','423',' '), null, 'number','contains char') dual 

first let's take @ translate function definition:

translate(expr, from_string, to_string): translate returns expr occurrences of each character in from_string replaced corresponding  character in to_string. characters in expr not in from_string not replaced.  if expr character string, must enclose in single quotation marks.  argument from_string can contain more characters to_string. in case,  characters @ end of from_string have no corresponding characters  in to_string. if these characters appear in char, removed  return value. 

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

now let's take @ our examples:

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

result of above function string consisted of whitespace - " "

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

result of above function empty string "". oracle database interprets empty string null, , if function has null argument, returns null.

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

result of above function whitespace string in first example.

that why getting "contains char" in first , third queries, , number in second one


Comments

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

caching - How to check if a url path exists in the service worker cache -

Redirect to a HTTPS version using .htaccess -