Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Apr 2011
    Posts
    28

    Unanswered: Like keyword with IN keyword

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    WHERE stateabb LIKE 'A%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    ;

  4. #4
    Join Date
    Apr 2011
    Posts
    28

    Red face

    Quote Originally Posted by tonkuma View Post
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ashu000 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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')
    )

  9. #9
    Join Date
    Apr 2011
    Posts
    28
    Hi Tonkuma,

    You are awesome. Thanks for being here.

    You are a real for us.


    Thanks once again.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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(...)
    Last edited by tonkuma; 07-02-11 at 15:10. Reason: Add SQL0132N. Add "Note: If ... SYSFUN.LOCATE(...)"

  11. #11
    Join Date
    Apr 2011
    Posts
    28
    Hi Tonkuma,

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

    Thanks once again. :-))
    Last edited by ashu000; 07-05-11 at 11:44.

  12. #12
    Join Date
    Apr 2011
    Posts
    28
    Quote Originally Posted by tonkuma View Post
    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.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 07-06-11 at 18:52. Reason: Number to examples such as "Example city-1:" and "Example city-2:"

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 || '%'
    ;
    Last edited by tonkuma; 07-07-11 at 02:09. Reason: Moved " || ','" to the first subselect of UNION ALL.

  15. #15
    Join Date
    Apr 2011
    Posts
    28
    Quote Originally Posted by tonkuma View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •