translate and instr

[dinzana]

Hello All,

Thanks in advance for your help. What is the following line of code trying to do?

translate(substr(<columnName>,
instr(<columnName>, , '|', 1, 1)+1,
instr(<columnName>, , '|', 1, 1)+1), '_0123456789','_')

Thanks again!

-dinzana

 

[Dagon]

It doesn't work. You have two commas after the first argument of the INSTR so, if you try to run it, you get "ORA-936, missing expression".

 

[SantaMufasa]

Dinzana,

...And even after you correct the syntax, the logic of the code seems a bit strange. I'll interpret for you the meaning of the nested functions:

1) Locate the first character position just beyond the first '|' in <columnName>. (Let's call that position <n>.)
2) From position <n>, for a length of <n> characters (which is the strange part of the logic), remove all numeric characters.
3) Return from the nested function, the contents of <columnName> after having removed just those specific numeric characters.

Let us know if this answers your question about what the code is trying to do.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]