Re: Separate word in a field

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. Received on Fri Dec 08 2006 - 10:45:14 CST