Re: PL/SQL substr(instr oracle

"Rauf Sarwar" <rs_arwar_at_hotmail.com> wrote in message news:92eeeff0.0306181306.1bf89859_at_posting.google.com...
> sp_786_at_hotmail.com (Pal) wrote in message
news:<d6c463ea.0306171002.3bc87902_at_posting.google.com>...
> > Hi
> >
> > can some one help to write a sql where I can extract string from the
> > field like this
> >
> >
> > YC_CHOICE_IS_MY_CHOICE
> >
> > YC_CIMC
> >
> > SP_786_at_YAHOO.COM
> >
> > THANK YOU IN ADVANCE
>
>
> I won't even consider putting this in PROD but just for kicks. If I
> understand your extract logic correctly... there are two variations.
>
> SQL> SET VERIFY OFF
> SQL> DEFINE STR = YC_CHOICE_IS_MY_CHOICE
> SQL> SELECT REPLACE(SUBSTR('&STR', 1, INSTR('&STR', '_', 1, 1)) ||
> TRANSLATE(INITCAP(REPLACE(SUBSTR('&STR', INSTR('&S
> TR', '_',1,1), LENGTH('&STR')), '_', ' ')),
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
> 'ABCDEFGHIJKLMNOP
> QRSTUVWXYZ'), ' ', '') AS STRING FROM DUAL;
>
> STRING
> -------
> YC_CIMC
>
> SQL> DEFINE STR = ABCD_EFGH_IJKL_MNOP_QRST
> SQL> /
>
> STRING
> ---------
> ABCD_EIMQ
>
> I am sure this can be done easily in PL/SQL but it's fun doing it in
> SQL -:)
>
> Regards
> /Rauf Sarwar

Rauf

Works for me.
Congrats.

eric Received on Thu Jun 19 2003 - 03:41:31 CDT