Re: PL/SQL substr(instr oracle

rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0306200736.f5ae063_at_posting.google.com>...

> it for yourself. I believe this is the query you are referring to, I
> just added ) after -1 to make it run,
>
> select substr('&STR',1,instr('&STR','_')-1)||
> translate(initcap(replace(substr('&STR',instr
> ('&STR','_')),'_',' ')),' abcdefghijklmnopqrstuvwxyz','_')
> from dual;
>
> YC_C_I_M_C
>

Oh well, Monday morning, fresh mind and a computer that works:

select ('&STR',1,instr('&STR','_'))||
translate(initcap(replace(substr('&STR',instr ('&STR','_')),'_',chr(15))),chr(1)||
'abcdefghijklmnopqrstuvwxyz'||chr(15),' ') from dual;

I was quite sure the outer replace was redundant. Hint: the TRANSLATE manual entry where it says "if these extra characters appear in char, they are removed". All I needed was to use the right characters! ;)  

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Sun Jun 22 2003 - 18:27:52 CDT