Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query with like does not accept wild cards

Posted on 2001-07-27
10
Medium Priority
?
448 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 8

Accepted Solution

by:
Dave_Greene earned 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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/…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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