Re: Separate word in a field

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Fri, 08 Dec 2006 16:51:13 +0100
Message-ID: <457989F1.6050006@arcor.de>

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 Received on Fri Dec 08 2006 - 09:51:13 CST