Solved

SQL query with like does not accept wild cards

Posted on 2001-07-27
10
441 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connection to multiple databases 13 25
If condition on Html with Asp 11 27
Insert Button on a table 16 37
Designing forms 3 17
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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