Separate word in a field

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 :) Received on Fri Dec 08 2006 - 02:06:06 CST