If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DB2 Query Using Where In with Wildcard

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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%')
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,103
What do you mean? Also please state your DB2 version and OS.

Andy
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
WHERE LEFT(partno,10) = '851545-403'
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,385
Quote:
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'
;
Reply With Quote
  #7 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,377
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Location: Richmond, VA
Posts: 1,036
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,385
Quote:
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%'
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,385
Quote:
... 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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On