Solved

SQL LIKE clause matches underscore?

Posted on 2007-04-03
14
7,133 Views
Last Modified: 2012-05-05
I have a SQL SELECT statement that has a clause:
  "WHERE filename LIKE " & sFileSpec
and when sFileSpec is "4_03" it returns a match for filename = "4303". Does the underscore character act like a wildcard, or does the LIKE clause match strings that only differ by one character, or is there another reason that it returns a match?
0
Comment
Question by:lee88
  • 4
  • 3
  • 2
  • +5
14 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18846735
change this:
          "WHERE filename LIKE " & sFileSpec
To:
 "WHERE filename LIKE '" & sFileSpec & "'"

to do a text match
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 18846737
The underscore is a single wildcard chararacter like the ? in Access queries.

"WHERE filename LIKE " & Replace(sFileSpec,"_", "\_"  & " ESCAPE '\'"

The ESCAPE clause tells SQL to treat the character after the backslash as a regular character, not a wildcard.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 18846741
Missed a closing paren.
"WHERE filename LIKE " & Replace(sFileSpec,"_", "\_")  & " ESCAPE '\'"
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 9

Expert Comment

by:TheSloath
ID: 18846744
_ is the same as ? in a like statement: it is a placemarker for any character.

Try "4[_]03"
0
 
LVL 32

Expert Comment

by:bhess1
ID: 18846747
Underscore is a wildcard character.  It matches any one character in that position in the string.  To look for an underscore, place brackets around the underscore, e.g.:

WHERE filename LIKE " & Replace(sFileSpec, "_","[_]")

This would leave the string value at 4[_]03
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18846749
Based on Books on Line the underscore acts like a single charaacter:

WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18846752
does the length of sFileSpec varies ot it is a 4 character string?


"WHERE Left(filename,4)='" & sFileSpec & "'"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18846764
look at that 4 replies at the same time....
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 18847067
Glad to be of assistance. May all your days get brighter and brighter.
0
 
LVL 22

Expert Comment

by:neeraj523
ID: 23835931
This solution did not work out for me,wonder why?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23836799
Hit the "ask a related question" link above and give us details. We should be able to help.
0
 
LVL 22

Expert Comment

by:neeraj523
ID: 23843696

guys.. what is this about ?? dont understand 'Open Discussion' section
0
 
LVL 22

Expert Comment

by:neeraj523
ID: 23843699


"neeraj523
03.09.2009 at 07:30PM IST, ID: 23835931
This solution did not work out for me,wonder why?"

I never posted this comment..
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 23846465
"Open Discussion" means this is being posted after the question is closed, so no points can be assigned.

neeraj523, if you didn't post something ascribed to you, I suggest you change your password.  Also, if co-workers have access to your computer, lock it when you step away from your desk.  There have been some jokes around my office based on failure to do that ...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 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