Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SUBSTR and INSTR
Try
select substr('yong', 1, decode(instr('yong',
'@'),0,length('yong'),instr('yong', '@')-1))
from dual
/
yong wrote in message <360AAF01.DC58A434_at_shell.com>...
>How can I get "yong" using one function (i.e. with no if-block) from
>both "yong_at_shell.com" and "yong"? I want to use substr and instr. For
>example,
>
>select substr('yong_at_shell.com', 1, instr('yong_at_shell.com', '@')-1) from
>dual;
>
>gives me "yong" correctly. But
>
>select substr('yong', 1, instr('yong', '@')-1) from dual;
>
>returns nothing. The instr function in the above select statement won't
>find '@' and it returns 0. So the substr function returns a 0-length
>string. How can I still get "yong" using functions like these or
>replace, translate etc.? Please help. Thanks.
>
>Yong Huang
>Email:yong_at_shell.com
>
Received on Fri Sep 25 1998 - 03:28:59 CDT