Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: return first token/word from string?
On Aug 22, 2:21 pm, jobs <j..._at_webdos.com> wrote:
> I'm on Oracle 9i. Is there any built in function that would return the
> first token or string from a string?
>
> For example, given string = " bob lfcr bill"
>
> I want to return "bob"
>
> imediately following bob there might be cases where we have a linefeed/
> cr.
>
> Thanks for any help or information.
You'll need instr() and substr() to do this in 9i:
substr(colname, 1, instr(colname, token, 1, 1) -1)
As an example:
SQL> create table instr_test(mycol varchar2(40));
Table created.
SQL> insert all
2 into instr_test
3 values ('bob and ed')
4 into instr_test
5 values ('carol and beulah')
6 into instr_test
7 values ('wilma and fred')
8 select * from dual;
3 rows created.
SQL> commit;
Commit complete.
SQL> select *
2 from instr_test;
MYCOL
SQL> select substr(mycol, 1, instr(mycol, ' ', 1, 1) -1) first_token 2 from instr_test;
FIRST_TOKEN
SQL> David Fitzjarrell Received on Wed Aug 22 2007 - 14:53:11 CDT