Charles Hooper schrieb:
> Maxim Demenko wrote:
>> RJ schrieb:
>>> Hi All,
>>>
>>> I have a table (table1) with 2 columns (ID, keywords)
>>> The ID column is the PK (values 1..n), and the keywords column is
>>> varchar with words separated with a space. EG:
>>>
>>> ID KEYWORD
>>> ---- -----------------
>>>
>>> 1 CAR VEHICLE
>>> 2 CHICKEN FOOD CUISINE
>>> 3 HOUSE PROPERTY CONSTRUCTION
>>>
>>> I need to separate the words to show like this:
>>>
>>> ID KEYWORD
>>> ----- -----------------
>>>
>>> 1 CAR
>>> 1 VEHICLE
>>> 2 CHICKEN
>>> 2 FOOD
>>> 2 CUISINE
>>>
>>> etc..
>>>
>>> how can this be done in SQL? I know i can use the UNION command to show
>>> each record, but how do I separate each word? I've tried using a
>>> combination of Instr and substr but its not showing correctly.
>>>
>>> Thanks in advance :)
>>>
>> Yet another approach ( assuming, between all words is exactly one space,
>> otherwise, it can be adjusted to reflect multiple spaces):
>>
>> scott_at_ORA102> create table table1
>> 2 (
>> 3 id number,
>> 4 keyword varchar2(4000)
>> 5 )
>> 6 ;
>> scott_at_ORA102> insert into table1 (id, keyword)
>> 2 values (1, 'CAR VEHICLE');
>> scott_at_ORA102> insert into table1 (id, keyword)
>> 2 values (2, 'CHICKEN FOOD CUISINE');
>> scott_at_ORA102> insert into table1 (id, keyword)
>> 2 values (3, 'HOUSE PROPERTY CONSTRUCTION');
>> scott_at_ORA102> with t as (
>> 2 select id, keyword,length(keyword)-length(replace(keyword,' '))+1
>> words
>> 3 from table1),
>> 4 g as (select rownum rn
>> 5 from dual
>> 6 connect by level<=(select
>> max(length(keyword)-length(replace(keyword,' ')))+1 from table1))
>> 7 select id,regexp_substr(keyword,'\w+',1,rn) word
>> 8 from t,g
>> 9 where g.rn<=t.words
>> 10 order by id,rn
>> 11 /
>>
>> ID WORD
>> ---------- ---------------
>> 1 CAR
>> 1 VEHICLE
>> 2 CHICKEN
>> 2 FOOD
>> 2 CUISINE
>> 3 HOUSE
>> 3 PROPERTY
>> 3 CONSTRUCTION
>>
>>
>> Best regards
>>
>> Maxim
>
> Maxim, I prefer your SQL statement to the one that I posted, although
> it would have been even more impressive if you would have explained how
> it worked (it only takes 2 minutes of experimentation to see how it
> works).
>
> SELECT
> ID,
> REGEXP_SUBSTR(KEYWORD,'\w+',1,number_of_word_here) WORD
> FROM
> TABLE1;
>
> I have been looking for a good reason to use REGEXP_SUBSTR, and it
> looks like the above is a very good use for this function.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>
Yes, for the purpose string splitting into tokens it might be very handy
to use it. Here is another nice example (courtesy www.sql.ru ) which
splits a string into words
scott_at_ORA102> SELECT LEVEL word_number,regexp_substr(s,'\w+',1,LEVEL) word
2 FROM ( SELECT 'Use new regexp functions in oracle 10g' s FROM dual)
3 CONNECT BY regexp_substr(s,'\w+',1,LEVEL) IS NOT NULL
4 /
WORD_NUMBER WORD
----------- ---------------
1 Use
2 new
3 regexp
4 functions
5 in
6 oracle
7 10g
Best regards
Maxim
Received on Fri Dec 08 2006 - 11:08:24 CST
