Re: Using LIKE with IN on an expression

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 27 Jan 2007 18:07:35 -0800
Message-ID: <1169950055.765693.274910@s48g2000cws.googlegroups.com>

On Jan 27, 12:55 pm, "Grant" <grant.Collinswo..._at_eds.com> wrote:
> Hi
>
> I should have qualified my earlier question... I am attempting to pass
> a variable containing multiple selections from a list box ( and in
> one instance , a string of comma delimited entries from a user text
> box.) I am kinda new to Oracle and wasn't sure whether I needed to
> use LIKE in conjunction with IN on the statement. As I understand,
> LIKE is a good way to broaden the range of possible returns, whereas
> IN is the way to enumerate and compare a list of values for a return.
> I was thinking that I could apply the benefit of LIKE on each member
> in the list collection. Im not really sure if that matters with an
> IN clause or not. If I say "IN ('%MyVarList%') I am not sure if it
> is constrained to an exact match or not) If I use LIKE with it,
> wouldn't that be less constrained on the matching criteria?
>
> Sorry if this sounds screwy...I am still novice to the DB world
>
> Thanks

Here is a quick example that shows one possible solution. First, the set up:
CREATE TABLE T1 (ANIMAL VARCHAR2(15));

INSERT INTO T1 VALUES ('COW');
INSERT INTO T1 VALUES ('PIG');
INSERT INTO T1 VALUES ('ZEBRA');
INSERT INTO T1 VALUES ('SHARK');
INSERT INTO T1 VALUES ('ROOSTER');
INSERT INTO T1 VALUES ('LION');

COMMIT; SELECT
*
FROM
T1;

ANIMAL

COW
PIG
ZEBRA
SHARK
ROOSTER
LION 6 rows selected.

Now, assume that there is a listbox where the user has entered several search keywords, one of which is LIO. If you would normally use something like this to find those records that contain "LIO" WHERE
  ANIMAL LIKE '%LIO%' You can instead use the INSTR function to do the same like this: WHERE
  INSTR(ANIMAL,'LIO') > 0 If you then had a larger list of keywords, you could structure the SQL statement like this:
SELECT
  ANIMAL
FROM
  T1
WHERE

  (INSTR(ANIMAL,'LIO')
  +INSTR(ANIMAL,'EB')
  +INSTR(ANIMAL,'ARK')
  +INSTR(ANIMAL,'DOG'))>0;

ANIMAL

ZEBRA
SHARK
LION Note that in the above, I just added the values returned by INSTR. If the value is greater than 0, then the row is returned.

Assume that you have a comma separated list of keyword values, where you would like to determine if the keywords can be found in the ANIMAL table that was created above. If we append a comma before the first keyword in the list and after the last keyword, we can then locate the commas and pick out the individual keywords using SUBSTR. If we start one character after the first comma, and then determine the number of characters between the first comma and the next comma, we can convert the comma separated values into separate rows that can eventually be joined to the ANIMAL table. We can use the DUAL table to generate a counter from 1 to 20. For example:
SELECT
SUBSTR(','||'LIO,EB,ARK,DOG'||',',

    INSTR(TRIM(','||'LIO,EB,ARK,DOG'||',')||',',',',1,LEVEL)+1,
    INSTR(TRIM(','||'LIO,EB,ARK,DOG'||',')||',',',',1,LEVEL+1)
    -(INSTR(TRIM(','||'LIO,EB,ARK,DOG'||',')||',',',',1,LEVEL)+1)

    ) KEYWORD
FROM
  DUAL
CONNECT BY
  LEVEL<=20;

KEYWORD

LIO
EB
ARK
DOG
(null)

20 ROWS SELECTED Now that we have the individual keywords on separate rows, we can slide the above into an inline view and join it to the ANIMAL table. SELECT
  ANIMAL
FROM
  T1,
  (SELECT
    SUBSTR(','||'LIO,EB,ARK,DOG'||',',

      INSTR(TRIM(','||'LIO,EB,ARK,DOG'||',')||',',',',1,LEVEL)+1,
      INSTR(TRIM(','||'LIO,EB,ARK,DOG'||',')||',',',',1,LEVEL+1)
      -(INSTR(TRIM(','||'LIO,EB,ARK,DOG'||',')||',',',',1,LEVEL)+1)
      ) KEYWORD

  FROM
    DUAL
  CONNECT BY
    LEVEL<=20)
WHERE
  KEYWORD IS NOT NULL
  AND ANIMAL LIKE '%'||KEYWORD||'%'; ANIMAL

LION
ZEBRA
SHARK 3 ROWS SELECTED There are other ways to accomplish the above.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jan 27 2007 - 20:07:35 CST