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 Wed Jun 18 2003 - 16:06:19 CDT
