Re: PL/SQL substr(instr oracle

Originally posted by Nuno Souto
> wizofoz2k_at_yahoo.com.au (Nuno Souto) wrote in message
> news:news:...
> >
> > this is better than my previous attempt:
> >
>
>
>
> Apologies:
>
> select substr('&STR',1,instr('&STR','_')-1||
> translate(initcap(replace(substr('&STR',instr
> ('&STR','_')),'_',' ')),' abcdefghijklmnopqrstuvwxyz','_')
> from dual;
>
>
>
> Cheers
> Nuno Souto
> wizofoz2k_at_yahoo.com.au.nospam

Good improvement Nuno. Uppercase alphabet in my original query was a bit much... but heck... it did work -:)

However, your query from previous post results in, YC_C_I_M_C

and your above query is missing a ) after -1 and also results in, YC C I M C

Here is the improvement to your above query... You need outside replace to remove spaces and you don't need -1 in first substr,

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

and yes... I would like to see how Sql Server or DB2 can do this.

Regards
/Rauf Sarwar

--
Posted via http://dbforums.com