SQL using LIKE '*' problem

[Doug4Now]

I am trying to open a recordset - ADO - that selects records on the basis of the first letter of a field eg. I want to find all the clients whose surname begins with "B". My SQL string works fine if I have

 ...LIKE 'Brown'

- I can see a client whose surname is Brown. However, if I write

 ...LIKE 'B*'

or

 ...LIKE '*'

the recordset is empty. It seems as though the "LIKE" operator is working as an "=" operator.

Any ideas about why this is happening and how it can be fixed.

Thanks, Doug.

 

[TomKane]

We use LIKE with SQL server and we use % instead of *

There is stuff relating to that in the online books of SQL Server - this may be of use to you:

Using Wildcard Characters as Literals
You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

Symbol Meaning
LIKE ‘'5[%]’ 5%
LIKE ‘[_]n’ _n
LIKE ‘[a-cdf]’ a, b, c, d, or f
LIKE ‘[-acdf]’ -, a, c, d, or f
LIKE ‘[ [ ]’ [
LIKE ‘]’ ]
LIKE ‘abc[_]d%’ abc_d and abc_de
LIKE ‘abc[def]’ abcd, abce, and abcf

 

[Doug4Now]

Hi Tom,

It worked - brilliant - thanks for sharing that.

I'm using VB6 with an Access 2000 backend - all the documentation/help files said that * is the wildcard, would never have thought to try % instead - not in a million years!

Thanks again, Doug.

 

[AndyWatt]

I've noticed this too.

I have one application using a MS Access v3 database (created with DAO 3.51) that uses "*" for a wild card, but another I recently created (using ADO 2.7) uses "%". A previous SQL Server application I wrote used "%". I think "%" is the "standard" wild card, as "*" can be used to mean "all fields".

Anyway that's my ha'penny's worth...


Regards,

Andy Watt

"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[CCLINT]

JET recognizes the * only.

ADO recognizes the % only, but JET interpets it as *

Sql statements can be sent directly to JET over ADO and then the * will be Interpeted correctly.

The ADO Filter method, which is client based and provider independent, can use either, I think.

I think in MS ACCESS, in the Query designer, an * is used, unless using a pass through query.