Like keyword with IN keyword

ashu000 · #1 (**permalink**) 07-01-11, 08:26

Hi All,

I need to create a query where I need to retrieve data from a table where user input will be used.
Let's say user enters values of 2 states as AA, AB. These 2 comma separated values will go with IN keyword as

SELECT * FROM STATE WHERE STATEABB IN ('AA', 'AB');

Now, my requirement is that user can enter only A in the input field, so I want to provide LIKE keyword functionality to send 'A%'.

I tried some ways but I am not able to generate the right query.

If someone have any idea about this. Kindly suggest me.

Thanks a lot.

r937 · #2 (**permalink**) 07-01-11, 09:28

Code:

WHERE stateabb LIKE 'A%'

tonkuma · #3 (**permalink**) 07-01-11, 09:29

An example:
(it may work, even if input was lower case.)

Code:

SELECT *
 FROM  state
 WHERE LOCATE( ',' || stateabb || ',' ,  ',' || UPPER(input) || ',' ) > 0
   OR  LEFT(stateabb , 1) = UPPER(input)
;

ashu000 · #4 (**permalink**) 07-01-11, 13:12

Quote:

Originally Posted by tonkuma

An example:
(it may work, even if input was lower case.)

Code:

SELECT *
 FROM  state
 WHERE LOCATE( ',' || stateabb || ',' ,  ',' || UPPER(input) || ',' ) > 0
   OR  LEFT(stateabb , 1) = UPPER(input)
;

Hi Tonkuma,

Thanks for your reply.
In my case, user can enter any number of alphabets. For example if state is Illinois, user can either enter I or Il or Ill etc. There can be more than 1 state in a comma separated fashion. So, I thought that somehow I have to use both IN and Like keyword.

Please suggest me the way for this requirement

r937 · #5 (**permalink**) 07-01-11, 15:04

Quote:

Originally Posted by ashu000

There can be more than 1 state in a comma separated fashion.

uh oh...

this is going to take special processing by your application language

you will want to generate " OR ... LIKE ... " conditions for each state

tonkuma · #6 (**permalink**) 07-01-11, 17:31

Quote:

In my case, user can enter any number of alphabets. For example if state is Illinois, user can either enter I or Il or Ill etc.

It will be better to clarify your requirements more.

If you wanted to check only abbreviation column and entered more tahn two characters for one of input abbreviaitions,
you should get first two characters for all input abbreviations.
or you might want to check state name column(I don't know you have the column in your table).

For example(this is an example, abbreviations may not be right.):
1) If you entered 'I,M',
do you want to get all of (Iowa, IA), (Idaho, ID), (Indiana, IN), (Illinois, IL), (Massachusetts, MA), (Maryland, MD), (Maine, ME), (Michigan, MI), (Minnesota, MN), (Missouri, MO), (Mississippi, MS), (Montana, MT)?

2) If you entered 'Ill,Mis',
do you want to get all of (Illinois, IL), (Missouri, MO), (Mississippi, MS)?
(this would be iimpossible, if you checked only abbreviaition column.)
or
do you want to get both of (Illinois, IL), (Michigan, MI)?
(matched abbreviaition column with first two characters of input abbreviations,
same as if input was 'Il,Mi'.)
or
other result?

tonkuma · #7 (**permalink**) 07-02-11, 04:41

Here is an example to get both of (Illinois, IL), (Michigan, MI)
for the input 'Ill,Mis'
(matched abbreviaition column with first two characters of input abbreviations,
same as if input was 'Il,Mi').

Tested on DB2 9.7 Fixpack 4 for Windows.
(In other DB2 version or fixpack, it may not work.)

Code:

SELECT *
 FROM  state
 WHERE ',' || UPPER(/*input:*/'Ill,Mis')        LIKE '%,' || state_abb || '%'
   OR  ',' || UPPER(/*input:*/'Ill,Mis') || ',' LIKE '%,' || LEFT(state_abb , 1) || ',%'
;
------------------------------------------------------------------------------

STATE_NAME           STATE_ABB
-------------------- ---------
Illinois             IL       
Michigan             MI       

  2 record(s) selected.

If input was 'I,M'

Code:

SELECT *
 FROM  state
 WHERE ',' || UPPER(/*input:*/'I,M')        LIKE '%,' || state_abb || '%'
   OR  ',' || UPPER(/*input:*/'I,M') || ',' LIKE '%,' || LEFT(state_abb , 1) || ',%'
;
------------------------------------------------------------------------------

STATE_NAME           STATE_ABB
-------------------- ---------
Iowa                 IA       
Idaho                ID       
Illinois             IL       
Indiana              IN       
Massachusetts        MA       
Maryland             MD       
Maine                ME       
Michigan             MI       
Minnesota            MN       
Missouri             MO       
Mississippi          MS       
Montana              MT       

  12 record(s) selected.

tonkuma · #8 (**permalink**) 07-02-11, 07:00

The data used in the previous examples was:

Code:

WITH
 state(state_name , state_abb) AS (
VALUES
  ('Alaska'         , 'AK') , ('Alabama'      , 'AL') , ('Arkansas'      , 'AR') , ('Arizona'       , 'AZ')
, ('California'     , 'CA') , ('Colorado'     , 'CO') , ('Connecticut'   , 'CT')
, ('District of Columbia' , 'DC') , ('Delaware' , 'DE')
, ('Florida'        , 'FL')
, ('Georgia'        , 'GA')
, ('Hawaii'         , 'HI')
, ('Iowa'           , 'IA') , ('Idaho'        , 'ID') , ('Illinois'      , 'IL') , ('Indiana'       , 'IN')
, ('Kansas'         , 'KS') , ('Kentucky'     , 'KY')
, ('Louisiana'      , 'LA')
, ('Massachusetts'  , 'MA') , ('Maryland'     , 'MD') , ('Maine'         , 'ME') , ('Michigan'      , 'MI')
, ('Minnesota'      , 'MN') , ('Missouri'     , 'MO') , ('Mississippi'   , 'MS') , ('Montana'       , 'MT')
, ('North Carolina' , 'NC') , ('North Dakota' , 'ND') , ('Nebraska'      , 'NE') , ('New Hampshire' , 'NH')
, ('New Jersey'     , 'NJ') , ('New Mexico'   , 'NM') , ('Nevada'        , 'NV') , ('New York'      , 'NY')
, ('Ohio'           , 'OH') , ('Oklahoma'     , 'OK') , ('Oregon'        , 'OR')
, ('Pennsylvania'   , 'PA')
, ('Rhode Island'   , 'RI')
, ('South Carolina' , 'SC')
, ('South Dakota'   , 'SD')
, ('Tennessee'      , 'TN') , ('Texas'        , 'TX')
, ('Utah'           , 'UT')
, ('Virginia'       , 'VA') , ('Vermont'      , 'VT')
, ('Washington'     , 'WA') , ('Wisconsin'    , 'WI') , ('West Virginia' , 'WV') , ('Wyoming'       , 'WY')
)

ashu000 · #9 (**permalink**) 07-02-11, 12:19

Hi Tonkuma,

You are awesome. Thanks for being here.

You are a real

for us.

Thanks once again.

tonkuma · #10 (**permalink**) 07-02-11, 13:47

If your DB2 doesn't support use of LIKE in the example(i.e. including a column name in pattern-expression, may be SQL0132N),
change
x LIKE '%' || y || '%'
to
LOCATE(y , x) > 0
, like...

Code:

SELECT *
 FROM  state
 WHERE LOCATE( ',' || state_abb                  , ',' || UPPER(/*input:*/'I,M')        ) > 0
   OR  LOCATE( ',' || LEFT(state_abb , 1) || ',' , ',' || UPPER(/*input:*/'I,M') || ',' ) > 0 
;
------------------------------------------------------------------------------

STATE_NAME           STATE_ABB
-------------------- ---------
Iowa                 IA       
Idaho                ID       
Illinois             IL       
Indiana              IN       
Massachusetts        MA       
Maryland             MD       
Maine                ME       
Michigan             MI       
Minnesota            MN       
Missouri             MO       
Mississippi          MS       
Montana              MT       

  12 record(s) selected.

or

Code:

SELECT *
 FROM  state
 WHERE LOCATE( ','||state_abb                , ','||UPPER(/*input:*/'Ill,Mis')      ) > 0
   OR  LOCATE( ','||LEFT(state_abb , 1)||',' , ','||UPPER(/*input:*/'Ill,Mis')||',' ) > 0 
;
------------------------------------------------------------------------------

STATE_NAME           STATE_ABB
-------------------- ---------
Illinois             IL       
Michigan             MI       

  2 record(s) selected.

Note: If the examples still doesn't work, try to add schema name explicitly, like
SYSFUN.LOCATE(...)

ashu000 · #11 (**permalink**) 07-05-11, 10:30

Hi Tonkuma,

I will try this for sure and let you know..

Thanks once again. :-))

ashu000 · #12 (**permalink**) 07-06-11, 11:45

Quote:

Originally Posted by tonkuma

Here is an example to get both of (Illinois, IL), (Michigan, MI)
for the input 'Ill,Mis'
(matched abbreviaition column with first two characters of input abbreviations,
same as if input was 'Il,Mi').

Tested on DB2 9.7 Fixpack 4 for Windows.
(In other DB2 version or fixpack, it may not work.)

Code:

SELECT *
 FROM  state
 WHERE ',' || UPPER(/*input:*/'Ill,Mis')        LIKE '%,' || state_abb || '%'
   OR  ',' || UPPER(/*input:*/'Ill,Mis') || ',' LIKE '%,' || LEFT(state_abb , 1) || ',%'
;
------------------------------------------------------------------------------

STATE_NAME           STATE_ABB
-------------------- ---------
Illinois             IL       
Michigan             MI       

  2 record(s) selected.

If input was 'I,M'

Code:

SELECT *
 FROM  state
 WHERE ',' || UPPER(/*input:*/'I,M')        LIKE '%,' || state_abb || '%'
   OR  ',' || UPPER(/*input:*/'I,M') || ',' LIKE '%,' || LEFT(state_abb , 1) || ',%'
;
------------------------------------------------------------------------------

STATE_NAME           STATE_ABB
-------------------- ---------
Iowa                 IA       
Idaho                ID       
Illinois             IL       
Indiana              IN       
Massachusetts        MA       
Maryland             MD       
Maine                ME       
Michigan             MI       
Minnesota            MN       
Missouri             MO       
Mississippi          MS       
Montana              MT       

  12 record(s) selected.

Hi Tonkuma,
The above mentioned method worked for me.

I am facing one problem that, my STATE table is having 1 column CITY. The same above mentioned query I created for CITY column as :

SELECT *
FROM state
WHERE ',' || UPPER(/*input:*/'Buff, deer') LIKE '%,' || city|| '%'
OR ',' || UPPER(/*input:*/'Buff, deer') || ',' LIKE '%,' || LEFT(city , 1) || ',%'
;

When I am running this query, I am getting no records where as expected result is

Buffalo Grove
Deerfield
DEERFIELD

I am not able to find the difference in the queries.

Could you please let me know what can be wrong.

Thanks in advance.

tonkuma · #13 (**permalink**) 07-06-11, 13:42

The differences between state_abb and city are
1) state_abb is fixed length 2chars.
2) state_abb contains only upper cases.
3) input for city contains a blank.

So, try this example.

Example city-1:

Code:

SELECT *
 FROM  state
 WHERE ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 1) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 2) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 3) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 4) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 5) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 6) || ',%'
...

Another way may be to decompose input into rows.

Example city-2: worked, but better to review more.

Code:

------------------------------ Commands Entered ------------------------------
WITH state(city) AS (
VALUES
  ('Buffalo Grove')
, ('Buffer zone')
, ('Deerfield')
, ('DEERFIELD')
, ('Deerhound')
, ('deep lake')
, ('deer')
)
SELECT *
 FROM  state
 INNER JOIN
       (SELECT LTRIM(
                  UPPER(
                     SUBSTR(
                        /*input:*/'Buff, deer'
                      , LAG(p , 1 , 0) OVER(ORDER BY p) + 1
                      , p - LAG(p , 1 , 0) OVER(ORDER BY p) - 1
                     )
                  )
               ) || '%'
         FROM  (VALUES 1,2,3,4,5,6,7,8,9
                      ,10,11,12,13,14,15,16,17,18,19
                      ,20,21,22,23,24,25,26,27,28,29
                      ,30,31,32,33,34,35,36,37,38,39
                      ,40,41,42,43,44,45,46,47,48,49
               ) p(p)
         WHERE p <= LENGTH(/*input:*/'Buff, deer') + 1
           AND SUBSTR(/*input:*/'Buff, deer' || ',' , MIN(p,LENGTH(/*input:*/'Buff, deer')+1) , 1) = ','
       ) t(city_abb)
   ON  UPPER(city) LIKE city_abb
;
------------------------------------------------------------------------------

CITY          CITY_ABB   
------------- -----------
Buffalo Grove BUFF%      
Buffer zone   BUFF%      
Deerfield     DEER%      
DEERFIELD     DEER%      
Deerhound     DEER%      
deer          DEER%      

  6 record(s) selected.

tonkuma · #14 (**permalink**) 07-06-11, 17:49

Other examples.

Example city-3: Revised version of city-2.

Code:

SELECT *
 FROM  state
 INNER JOIN
       (SELECT LTRIM(
                  UPPER(
                     SUBSTR(
                        input
                      , LAG(p , 1 , 0) OVER(ORDER BY p) + 1
                      , p - LAG(p , 1 , 0) OVER(ORDER BY p) - 1
                     )
                  )
               )
         FROM  (VALUES 'Buff, deer') parm(input)
         INNER JOIN
               LATERAL
               (SELECT p1 + p2 * 10 + p3 * 100 AS p
                 FROM  (VALUES    1, 2, 3, 4, 5, 6, 7, 8, 9, 10) p(p1)
                 INNER JOIN
                       (VALUES 0, 1, 2, 3, 4, 5, 6, 7, 8, 9    ) p(p2)
                   ON  p1 + p2 * 10 <= LENGTH(input) + 1
                 INNER JOIN
                       (VALUES 0, 1, 2, 3, 4, 5, 6, 7, 8, 9    ) p(p3)
                   ON  p1 + p2 * 10 + p3 * 100 <= LENGTH(input) + 1
               )
           ON  SUBSTR(input || ',' , p , 1) = ','
       ) t(city_abb)
   ON  UPPER(city) LIKE city_abb || '%'
;

Example city-4:

Code:

WITH
 decompose(u_input , city_abb , p , k) AS (
VALUES
( UPPER(/*input:*/'Buffa, deer') || ','
, CAST('' AS VARCHAR(20) )
, 0 , 0
)
UNION ALL
SELECT u_input
     , LTRIM( SUBSTR(u_input , p + 1 , n_p - p - 1) )
     , n_p
     , k + 1
 FROM  (SELECT d.*
             , LOCATE(',' , u_input , p + 1) AS n_p
         FROM  decompose d
         WHERE k < 1000
       )
 WHERE n_p > 0
)
SELECT *
 FROM  state
 INNER JOIN
       decompose
   ON  p > 0
   AND UPPER(city) LIKE city_abb || '%'
;

ashu000 · #15 (**permalink**) 07-07-11, 14:07

Quote:

Originally Posted by tonkuma

The differences between state_abb and city are
1) state_abb is fixed length 2chars.
2) state_abb contains only upper cases.
3) input for city contains a blank.

So, try this example.

Example city-1:

Code:

SELECT *
 FROM  state
 WHERE ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 1) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 2) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 3) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 4) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 5) || ',%'
   OR  ',' || REPLACE(UPPER(/*input:*/'Buff, deer') , ' ' , '') || ',' LIKE '%,' || LEFT(UPPER(city) , 6) || ',%'
...

Another way may be to decompose input into rows.

Example city-2: worked, but better to review more.

Code:

------------------------------ Commands Entered ------------------------------
WITH state(city) AS (
VALUES
  ('Buffalo Grove')
, ('Buffer zone')
, ('Deerfield')
, ('DEERFIELD')
, ('Deerhound')
, ('deep lake')
, ('deer')
)
SELECT *
 FROM  state
 INNER JOIN
       (SELECT LTRIM(
                  UPPER(
                     SUBSTR(
                        /*input:*/'Buff, deer'
                      , LAG(p , 1 , 0) OVER(ORDER BY p) + 1
                      , p - LAG(p , 1 , 0) OVER(ORDER BY p) - 1
                     )
                  )
               ) || '%'
         FROM  (VALUES 1,2,3,4,5,6,7,8,9
                      ,10,11,12,13,14,15,16,17,18,19
                      ,20,21,22,23,24,25,26,27,28,29
                      ,30,31,32,33,34,35,36,37,38,39
                      ,40,41,42,43,44,45,46,47,48,49
               ) p(p)
         WHERE p <= LENGTH(/*input:*/'Buff, deer') + 1
           AND SUBSTR(/*input:*/'Buff, deer' || ',' , MIN(p,LENGTH(/*input:*/'Buff, deer')+1) , 1) = ','
       ) t(city_abb)
   ON  UPPER(city) LIKE city_abb
;
------------------------------------------------------------------------------

CITY          CITY_ABB   
------------- -----------
Buffalo Grove BUFF%      
Buffer zone   BUFF%      
Deerfield     DEER%      
DEERFIELD     DEER%      
Deerhound     DEER%      
deer          DEER%      

  6 record(s) selected.

Hi Tonkuma,

The VALUES 1,2,3,4,5,6,7,8,9
,10,11,12,13,14,15,16,17,18,19
,20,21,22,23,24,25,26,27,28,29
,30,31,32,33,34,35,36,37,38,39
,40,41,42,43,44,45,46,47,48,49, you have provided in the above code. Could you please let me know the purpose of providing values upto 49.

Similarly, in more examples also, you have taken values upto 10 and at some places 9. I am not understanding how you are deciding this limit.

Thanks for all your help and time.

Internet Services

Web Development

Digital Marketing

Consumer Tech