Re: Separate word in a field

Yes it is bad data modeling, I'm trying to fix it. If theres no simple way of doing it in SQL i'll have to do as you say with the CSV file.

Any ideas?

On Dec 8, 10:20 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> 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:50:37 CST