Solved

SQL query with like does not accept wild cards

Posted on 2001-07-27
10
438 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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/…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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