Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2009
    Posts
    14

    DB2 Query Using Where In with Wildcard

    I need help getting the following concept to work. I need the SQL to return all A% possibilities.

    Select recseq From myFile Where partno In('851545-401',851545-401A%')

  2. #2
    Join Date
    Jan 2003
    Posts
    4,147
    What do you mean? Also please state your DB2 version and OS.

    Andy

  3. #3
    Join Date
    Aug 2009
    Posts
    14
    Quote Originally Posted by ARWinner View Post
    What do you mean? Also please state your DB2 version and OS.

    Andy
    DB2 ver 8.0
    OS iSeries V5r3

    Suppose I have records 851545-4, 851545-4E, 851545-403, 851545-403A, 851545-403AG these have a datetime stamp field.

    I want to know the latest record with part number 851545-403 regardless of suffix. Using the LIKE and % wildcard will work until I want to look up part number 851545-4 which would return all the 851545-403's.

    So my thought was to use this:

    Select partnumber From myFile Where partno in('851545-403','851545-403A%')

    But this will not work as I thought because it only will return 851545-403.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE LEFT(partno,10) = '851545-403'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2009
    Posts
    14
    Quote Originally Posted by r937 View Post
    WHERE LEFT(partno,10) = '851545-403'
    This would not work if my part number is 851545-4 as it would return all the -403's with it.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Suppose I have records 851545-4, 851545-4E, 851545-403, 851545-403A, 851545-403AG these have a datetime stamp field.

    I want to know the latest record with part number 851545-403 regardless of suffix. Using the LIKE and % wildcard will work until I want to look up part number 851545-4 which would return all the 851545-403's.
    How about this?

    Code:
    SELECT partno
      FROM myFile
     WHERE TRANSLATE(partno , '' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
           = '851545-403'
    ;

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,460
    Quote Originally Posted by SDyke View Post

    So my thought was to use this:

    Select partnumber From myFile Where partno in('851545-403','851545-403A%')

    But this will not work as I thought because it only will return 851545-403.
    Have you heard about the logical operator "OR"? It might come useful here.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,122
    or go the easy route and say:
    Code:
    Select partnumber From myFile Where partno = '851545-403'
    union
    Select partnumber From myFile Where partno like '851545-403A%'
    Dave

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Select partnumber From myFile Where partno in('851545-403','851545-403A%')
    Even if used like:
    Code:
    SELECT partno
      FROM myFile
     WHERE partno = '851545-403'
       OR  partno LIKE '851545-403A%'
    How do you know the suffix 'A'?

    If partno is 851545-4, you may want to use like:
    Code:
    SELECT partno
      FROM myFile
     WHERE partno = '851545-4'
       OR  partno LIKE '851545-4E%'

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma View Post
    Even if used like:
    Code:
    SELECT partno
      FROM myFile
     WHERE partno = '851545-403'
       OR  partno LIKE '851545-403A%'
    How do you know the suffix 'A'?

    If partno is 851545-4, you may want to use like:
    Code:
    SELECT partno
      FROM myFile
     WHERE partno = '851545-4'
       OR  partno LIKE '851545-4E%'
    This solution is good, but not a universal.
    Lenny

  11. #11
    Join Date
    Aug 2009
    Posts
    14
    Quote Originally Posted by n_i View Post
    Have you heard about the logical operator "OR"? It might come useful here.
    Where or how do you propose I use the OR operator. I have tried seveal different combiations but now work.

  12. #12
    Join Date
    Aug 2009
    Posts
    14
    Quote Originally Posted by tonkuma View Post
    How about this?

    Code:
    SELECT partno
      FROM myFile
     WHERE TRANSLATE(partno , '' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
           = '851545-403'
    ;
    This produces the result I need but it is super slow. Can anything be done to speed it up?

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    Quote Originally Posted by SDyke View Post
    This produces the result I need but it is super slow. Can anything be done to speed it up?
    This one will work much faster, if partno is part of the index:

    Code:
    SELECT partno
      FROM myFile
     WHERE 
    partno like '851545-403%'
    And
    TRANSLATE(partno , '' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
           = '851545-403'
    ;
    Lenny

  14. #14
    Join Date
    Aug 2009
    Posts
    14
    Quote Originally Posted by Lenny77 View Post
    This one will work much faster, if partno is part of the index:

    Code:
    SELECT partno
      FROM myFile
     WHERE 
    partno like '851545-403%'
    And
    TRANSLATE(partno , '' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
           = '851545-403'
    ;
    Lenny
    That works some better but I just found another issue. The parts can be made at different locations. So the 15th character position can have a lettter in it like M for Mexico. Is there any way to take what you have done here and make it work if there is a character in the 15th position?

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... So the 15th character position can have a lettter in it like M for Mexico. ...
    Question 1) Is "the 15th character position" fixed?

    Question 2) How do you distinguish "a letter at the 15th character position" is a part of a partno and "a letter at the 15th character position" is a suffix?

    Are there any partno like 851545-4M?

    It will be better to redifine/restate your requirements throughly including all possible formats of partno.

    After someone(including me) showed some solutions, you added another requirements and/or another possibility of partno formats.
    It is very inefficient and irritate me.

Posting Permissions

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