Re: PL/SQL substr(instr oracle

"Nuno Souto" <wizofoz2k_at_yahoo.com.au> wrote in message news:73e20c6c.0306221527.54332163_at_posting.google.com...
> 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

I don't believe you need the 'replace', but then there was no spec.

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

eric Received on Mon Jun 23 2003 - 05:24:21 CDT