Oracle Word Extraction - Please Help

[lmtai]

Could anyone provide the SQL statement on how to extract a single word (the first word) from a string in Oracle 8 ?

Please help , statement urgently required. Thanks.

 

[ddrillich]

If we assume that the first word is followed by a space then the following can be useful:

select substr('Merci beaucoup Dave!',1,instr('Merci beaucoup Dave!',' ',1,1)) from dual;

It will return Merci.

Cheers,
Dan

 

[ddrillich]

A bit more generic solution would be to replace all the word separators by a non-used character and then get the first word, as in:

select substr(translate('Merci beaucoup Dave!',' -;','~'),1,instr(translate('Merci beaucoup Dave!',' -;','~'),'~',1,1)-1) from dual;

Cheers,
Dan

 

[SantaMufasa]

Imtai,

Here is my FIRST_WORD function (which returns the characters in a string that precede any of these symbols that appear between the ticks: ' ,.!?;'):

create or replace function first_word (str in varchar2) return varchar2 is
	First_word_hold	varchar2(100);
	str_hold	varchar2(4000);
	end_of_word_loc number;
begin
	str_hold	:= translate(str,' ,.!?;','^^^^^^');
	end_of_word_loc	:= instr(str_hold,'^');
	first_word_hold	:= substr(str_hold,1,end_of_word_loc-1);
	return first_word_hold;
end;
/

Code Test:
col a heading "First|Word" format a10
col b heading "Entire|Comment" format a30
select first_word(comments) a, comments b from participants
where comments is not null;

First      Entire
Word       Comment
---------- ---------------------
Avail      Avail 2/13,20,27 only
Couple     Couple if poss.
Not        Not Tu a.m.
Recommend  Recommend Desk
Also       Also Sat 1,2
Prefer     Prefer couple
Prefer     Prefer couple

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:53 (27Nov03) GMT, 20:53 (26Nov03) Mountain Time)

 

[lmtai]

Hi Dan and Mufasa
Thank you very much for your quick replies. Managed to solve my problem