Wild Cards

[mpdillon]

I am using VB 6 with ADO to query a MS SQL database. I need to return all the records which match my criteria. I can explain it in words but not in code.
Verbally, here is what I am trying to do with a field called ItemNo.

I am given ItemNo = 415.

The table contains 415, ?15, ??5, 4?6, 4?9, ??9

In the table the ? represnts a wild card. Any character appearing in the same location is considered a match.
So from my table 415, ?15, ??5 all would match my given ItemNo, 415. Can a SQL statement be written to return the recordset I am looking for?

Note: The real ItemNo I am looking for is much longer and there are tens of thousands of records to search.

Any help would be appreciated.
Thanks,
pat

 

[johnwm]

MS SQL uses an underscore character to match a single character in a text field. Assuming this is a text field that you are searching, you will also need the Union operator to collect the data into one recordset.

Select * From myTable
Where myText Like '4__'
Union Select * From myTable
Where myText Like '_1_'
Union Select * From myTable
Where myText Like '__5'

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Dimandja]

Look into LIKE:

http://www.1keydata.com/sql/sqllike.html

 

[mpdillon]

I always learn something when I post a question. Thanks John for your reply. Your reply almost does what I need. I have never seen the union command used so thanks much for the example. The 4__ would return anything with a 4 in the first space. What I need is it to return any string that has a 4 or a ? in the first place. And then only if there is a match in the other spaces(1 or ? in the second place and 5 or ? in the third place). The database I am querying reprsents a listing of Product specifications. In the simplest of scenarios, every product would have a specific Procut specification and it would be named the same as the item number. If I wanted to retrieve the product specification for 415 there would only be one match in the database and it would be labeled 415. But in the actual problem I am working on, the customer didn't want to make all those product specification. Rather they made some product specifications that would fit many item numbers. They used the ? to desigate a space where any number would match. So if there weren't an exact specification for 415 but there was a specification for ?15, I need a select statement that will find the ?15 entry in the database. Also, ??5, 7?5, 71?, etc. would also match and need to be returned. When there is more than one match to my query there is a sort key which enables me to choose the match to use.

Dimandja, I looked over the link you sent but it is not quite it. The same problem as above. But I did find the IN command on that page. It helps with the kludge I am using. I know I have 10 character, so in addition to the itemno field, there are 10 other fields, each representing a space in the item no. I did this so I could use the follwing select statement;
Select ItemNo From Table where (Pos1 = '4' or Pos1 = '10') and (pos2 = '1' or Pos2 = '10') and (pos3 = '5' or pos3 = '10') NOTE: I substituted "10" for "?" before I save the record. I didn't know what effect the ? would have on searches.
With the IN command the same select becomes...
Select ItemNo from Table where Pos1 IN ('7','10) and Pos2 IN ('1','10') and Pos3 ('5','10')

Now thinking without test would...
Select ItemNo From Table where ItemNo IN ('7__','?__')
union select ItemNo from Table where ItemNo IN ('_1_','_?_')
union select ItemNo from Table where ItemNo IN ('_5_','__?')
I will test later and post the result.
Thanks for your help

 

[johnwm]

That's what the Union operator does - it combines the 3 returned recordsets into one. In my example it would return all records with a 4 in the first position, and all the records with a 1 in the second position, and all the records with a 5 in the third position

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'