Re: return first token/word from string?

On Aug 22, 3:53 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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
> ----------------------------------------
> bob and ed
> carol and beulah
> wilma and fred
>
> SQL> select substr(mycol, 1, instr(mycol, ' ', 1, 1) -1) first_token
> 2 from instr_test;
>
> FIRST_TOKEN
> ----------------------------------------
> bob
> carol
> wilma
>
> SQL>
>
> David Fitzjarrell

Thanks, but what if the first token is not in position 1?

select substr(' bob and mary', 1, instr(' bob and mary', ' ', 1, 1) -1) from dual;

returns nothing.

Also, the first token may be the only token, and may also be immediately followed by an linefeed.

Thanks again. Received on Wed Aug 22 2007 - 14:59:36 CDT