Wildcard SELECT with a LIKE clause, using the '_' character
I ran into a problem recently trying to find usernames that contained the '_' character. So having forgotten some of my more in depth SQL skills, I wrote the following query.
SELECTTOP 10 * FROM Account a WHERE a.Username LIKE'%_%'
The reason this did not return any results, was because '_' is actually a wildcard character itself, matching a single character position as opposed to many. So it turns out that the character needs to be escaped. In order to escape the '_' you need to use the ESCAPE keyword.
SELECTTOP 10 * FROM Account a WHERE a.Username LIKE'%\_%'ESCAPE'\'
By adding ESCAPE '\' to the statement, it makes the \ an escape character, which is then used to escape the _.
Written By: mycodeshare on March 13, 2009 at 21:52
Submit a comment, suggestion, or additional information about this snippet
If you login or register,
you'll be able to post a comment or provide feedback about this snippet.