Re: Separate word in a field

On 08.12.2006 09:06, RJ wrote:
> 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.

IMHO this is a typical case of bad data modeling. I would want to correct that. I'd probably write a script that extracts the data and writes out the proper separated values to a CSV file. Then I'd change the table layout or constraints (depending on what's there and how it's used) and import the data.

Kind regards

        robert Received on Fri Dec 08 2006 - 02:20:49 CST