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 13:13:33 -0700
Message-ID: <1187813613.141907.21040@q3g2000prf.googlegroups.com>


On Aug 22, 2:59 pm, jobs <j..._at_webdos.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Please note I said 'As an example', not 'As the absolutely, positively correct method barring all others'. Examples ILLUSTRATE the concept, and, HOPEFULLY, you understood the illustration.

It's up to you to modify the example code to fit YOUR particular situation.

David Fitzjarrell Received on Wed Aug 22 2007 - 15:13:33 CDT

Original text of this message

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