Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL substr(instr oracle

Re: PL/SQL substr(instr oracle

From: Ethel Aardvark <bigjobbies_at_hotmail.com>
Date: 19 Jun 2003 05:29:49 -0700
Message-ID: <1a8fec49.0306190429.7c840ddb@posting.google.com>


Show-off!

ETA rs_arwar_at_hotmail.com (Rauf Sarwar) 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
Received on Thu Jun 19 2003 - 07:29:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US