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

Home -> Community -> Usenet -> c.d.o.misc -> Re: return first token/word from string?

Re: return first token/word from string?

From: <fitzjarrell_at_cox.net>
Date: Wed, 22 Aug 2007 12:53:11 -0700
Message-ID: <1187812391.950080.126970@i38g2000prf.googlegroups.com>


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 Received on Wed Aug 22 2007 - 14:53:11 CDT

Original text of this message

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