Re: Separate word in a field

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 8 Dec 2006 03:53:56 -0800
Message-ID: <1165578836.126525.273580@79g2000cws.googlegroups.com>

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.
>
> Thanks in advance :)

On the surface, this appears to be very difficult. There are a couple different ways that this can be done. Since you are working with INSTR and SUBSTR, we will use that method.

First, the set up:
CREATE TABLE TABLE1 (ID NUMBER(10), KEYWORD VARCHAR2(255));

INSERT INTO TABLE1 VALUES (1,'CAR VEHICLE');
INSERT INTO TABLE1 VALUES (2,'CHICKEN FOOD CUISINE');
INSERT INTO TABLE1 VALUES (3,'HOUSE PROPERTY CONSTRUCTION');

And just for fun:
INSERT INTO TABLE1 VALUES (4,'MOOSE DUCK BEAR COW PIG DOG CAT MOUSE RAT HAMSTER LION TIGER'); If we can trim the KEYWORD column to eliminate any leading and trailing spaces, it will make the task much easier. To keep the task simple, we will then add a space to the end of the KEYWORD column when making comparisons.

SELECT
  TRIM(KEYWORD)||' ' NEW_KEYWORD
FROM
  TABLE1; With this change, we can using INSTR to find the first, second, third, etc. space in the KEYWORD column:
SELECT
  ID,

  INSTR(TRIM(KEYWORD)||' ',' ',1,1) SPACE_1,
  INSTR(TRIM(KEYWORD)||' ',' ',1,2) SPACE_2,
  INSTR(TRIM(KEYWORD)||' ',' ',1,3) SPACE_3,
  INSTR(TRIM(KEYWORD)||' ',' ',1,4) SPACE_4,
  INSTR(TRIM(KEYWORD)||' ',' ',1,5) SPACE_5,
  INSTR(TRIM(KEYWORD)||' ',' ',1,6) SPACE_6,
  INSTR(TRIM(KEYWORD)||' ',' ',1,7) SPACE_7,
  INSTR(TRIM(KEYWORD)||' ',' ',1,8) SPACE_8,
  INSTR(TRIM(KEYWORD)||' ',' ',1,9) SPACE_9,
  INSTR(TRIM(KEYWORD)||' ',' ',1,10) SPACE_10,
  INSTR(TRIM(KEYWORD)||' ',' ',1,11) SPACE_11,
  INSTR(TRIM(KEYWORD)||' ',' ',1,12) SPACE_12,
  INSTR(TRIM(KEYWORD)||' ',' ',1,13) SPACE_13,

  TRIM(KEYWORD)||' ' NEW_KEYWORD
FROM
  TABLE1; ID S_1 S_2 S_3 S_4 S_5 S_6 S_7 S_8 S_9 S_10 S_11 S_12 S_13 NEW_KEYWORD 1 4 12 0 0 0 0 0 0 0 0 0 0 0 CAR VEHICLE 3 6 15 28 0 0 0 0 0 0 0 0 0 0 HOUSE PROPERTY CONSTRUCTION
2 8 13 21 0 0 0 0 0 0 0 0 0 0 CHICKEN FOOD CUISINE
4 6 11 16 20 24 28 32 38 42 50 55 61 0 MOOSE DUCK BEAR COW PIG DOG CAT MOUSE RAT HAMSTER LION TIGER Now that we know where the spaces are located in the KEYWORD column, if we slide the above into an inline view, we can use SUBSTR to pull out each of the keywords. If one of the SPACE_n values is 0, that indicates that the keyword in the previous position does not exist, so we need to use DECODE to check the SPACE_n value: SELECT
  ID,
  DECODE(SPACE_1,0,NULL,SUBSTR(NEW_KEYWORD,1,SPACE_1-1)) SUB_KEYWORD1,   DECODE(SPACE_3,0,NULL,SUBSTR(NEW_KEYWORD,SPACE_2+1,SPACE_3-SPACE_2)) SUB_KEYWORD3,
  NEW_KEYWORD
FROM
  (SELECT
    ID,

    TRIM(KEYWORD)||' ' NEW_KEYWORD,
    INSTR(TRIM(KEYWORD)||' ',' ',1,1) SPACE_1,
    INSTR(TRIM(KEYWORD)||' ',' ',1,2) SPACE_2,
    INSTR(TRIM(KEYWORD)||' ',' ',1,3) SPACE_3,
    INSTR(TRIM(KEYWORD)||' ',' ',1,4) SPACE_4,
    INSTR(TRIM(KEYWORD)||' ',' ',1,5) SPACE_5,
    INSTR(TRIM(KEYWORD)||' ',' ',1,6) SPACE_6,
    INSTR(TRIM(KEYWORD)||' ',' ',1,7) SPACE_7,
    INSTR(TRIM(KEYWORD)||' ',' ',1,8) SPACE_8,
    INSTR(TRIM(KEYWORD)||' ',' ',1,9) SPACE_9,
    INSTR(TRIM(KEYWORD)||' ',' ',1,10) SPACE_10,
    INSTR(TRIM(KEYWORD)||' ',' ',1,11) SPACE_11,
    INSTR(TRIM(KEYWORD)||' ',' ',1,12) SPACE_12,
    INSTR(TRIM(KEYWORD)||' ',' ',1,13) SPACE_13

  FROM
    TABLE1);

ID SUB_KEYWORD1	SUB_KEYWORD3  NEW_KEYWORD
1  CAR                        CAR VEHICLE
3  HOUSE        CONSTRUCTION  HOUSE PROPERTY CONSTRUCTION
2  CHICKEN      CUISINE       CHICKEN FOOD CUISINE
4  MOOSE        BEAR          MOOSE DUCK BEAR COW PIG DOG CAT MOUSE RAT

HAMSTER LION TIGER If you make the above a named query in a SELECT statement using WITH, you could then use a UNION ALL to pull out all of the non-null SUB_KEYWORD1 values with their associated IDs, non-null SUB_KEYWORD2 values with their associated IDs, SUB_KEYWORD3 values with their associated IDs, etc.

There are probably more efficient methods for accomplishing the above.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Dec 08 2006 - 05:53:56 CST