hey thanx man!
On Dec 8, 1:53 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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 - 07:11:42 CST
