Re: return first token/word from string?

"jobs" <jobs_at_webdos.com> schreef in bericht news:1187812776.868631.69690_at_e9g2000prf.googlegroups.com...
> 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.
>

Then use LTRIM on your string before processing it.

Shakespeare Received on Thu Aug 23 2007 - 03:34:06 CDT