SQL LIKE clause matches underscore?

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?
lee88Asked:
Who is Participating?
 
Jim P.Commented:
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
 
puppydogbuddyCommented:
change this:
          "WHERE filename LIKE " & sFileSpec
To:
 "WHERE filename LIKE '" & sFileSpec & "'"

to do a text match
0
 
Jim P.Commented:
Missed a closing paren.
"WHERE filename LIKE " & Replace(sFileSpec,"_", "\_")  & " ESCAPE '\'"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TheSloathCommented:
_ is the same as ? in a like statement: it is a placemarker for any character.

Try "4[_]03"
0
 
Brendt HessSenior DBACommented:
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
 
rboyd56Commented:
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
 
Rey Obrero (Capricorn1)Commented:
does the length of sFileSpec varies ot it is a 4 character string?


"WHERE Left(filename,4)='" & sFileSpec & "'"
0
 
Rey Obrero (Capricorn1)Commented:
look at that 4 replies at the same time....
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
 
neeraj523Commented:
This solution did not work out for me,wonder why?
0
 
Jim P.Commented:
Hit the "ask a related question" link above and give us details. We should be able to help.
0
 
neeraj523Commented:

guys.. what is this about ?? dont understand 'Open Discussion' section
0
 
neeraj523Commented:


"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
 
Daniel WilsonCommented:
"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.