Solved

SQL query with like does not accept wild cards

Posted on 2001-07-27
10
439 Views
Last Modified: 2012-08-13
Hi All

I seem to be experiencing a strange problem. I am using ADODB to read an Access Database on an IIS server (actually I'm testing it with PWS). When I open a recordset with an sql query containing a 'where x like y' clause I am unable to use wild cards. If I use the * wild card then I get no records returned. A code segment is as below

<%
     whereClause = "WHERE Material Like '*Silk*'"
     
     Call OpenConnection2(objConn,objRS)
     strSQL = "SELECT * FROM [Stock List] " & whereClause & " order by [Stock No];"
     Response.Write "<BR>" & strSQL
     objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly %>

If however I remove the wild card a get the expected results.

Anybody got any aideas as to what I am doing wrong?

Many thanks

Steve
0
Comment
Question by:spwiggins
  • 5
  • 4
10 Comments
 
LVL 8

Accepted Solution

by:
Dave_Greene earned 100 total points
ID: 6328223
Use Like %Silk%

% instead of *
0
 
LVL 7

Expert Comment

by:John844
ID: 6328225
access has problems with the wildcards being on both sides

try removing the wildcard from one of the sides

if this works for you then change your where statement to something like
where (field like '*test' or field like 'test*')
0
 
LVL 7

Expert Comment

by:John844
ID: 6328233
I think that access will generate an error when you use %.  SQL needs to use the % as wildcard character.  Access uses * for the wildcard character.
0
 
LVL 7

Expert Comment

by:John844
ID: 6328238
example with your code
whereClause = "WHERE (Material Like '*Silk' or Material LIKE 'Silk*')"
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328244
Trust me use the '%' Character...

Cheers!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Expert Comment

by:John844
ID: 6328257
what version do you get the % character to work on.  Just tested on Access97 and it does not work.  I was able to get it to work using *.  I was also able to use the * on both sides of the word like '*test*'.
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328268
Access 97 & 2000.  Here is an example query.

        strQry = "SELECT * FROM tblIssues WHERE System LIKE '" & Trim(txtADVsearch.Text) & "%' " & _
            "OR OS LIKE '" & Trim(txtADVsearch.Text) & "%' OR Application LIKE '" & Trim(txtADVsearch.Text) & "%' " & _
            "OR Hardware LIKE '" & Trim(txtADVsearch.Text) & "%' OR Peripherals LIKE '" & Trim(txtADVsearch.Text) & "%' " & _
            "OR Other LIKE '" & Trim(txtADVsearch.Text) & "%'"

But the problem is that you cannot do a full text value searching with Access.  It is a new feature in SQL2000
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328277
John844,

Are you making the query from inside access?  If so then the * will work.  If you are using ODBC, the * won't work
0
 

Author Comment

by:spwiggins
ID: 6328287
Thanks Dave for a speedy response

Steve
0
 
LVL 7

Expert Comment

by:John844
ID: 6328294
thanks for the info Dave.  I was running the query from inside access.  I did have the same problems spwiggins is describing and Fixed it using the method '*test' or 'test*'.  I had not used SQL back then though(been a while).
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Smart quotes being changed on insert 9 50
Issue with Loop 4 50
Query to get the soonest date out of a few records 2 43
Classic ASP application Will support SQL 2014 5 76
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now