SEARCH

How-To Geek

Search For Rows With Special Characters in SQL Server

While troubleshooting a programming problem today I noticed that you can’t use a LIKE search for string columns containing special characters like % or _ without using a special syntax. Figuring out the problem took only a few minutes, but remembering syntax is always easier if you write about it.

So yes, this post is solely for my benefit. Hopefully it will help somebody else too.

Let’s say you want to find any fields that contain the text “100%”, so you put together this query:

SELECT * FROM tablename WHERE fieldname LIKE ‘%100%%’

Instead of what you wanted, you’ll get all the rows that contain “100″ as well as the rows that contain “100%”.

The problem here is that SQL Server uses the percent sign, underscore, and square brackets as special characters. You simply can’t use them as a plain character in a LIKE query without escaping them.

Square Bracket Escape

You can surround the % or _ with square brackets to tell SQL Server that the character inside is a regular character.

SELECT * FROM tablename WHERE fieldname LIKE ‘%100[%]%’

T-SQL ESCAPE Syntax

Alternatively, you can append the ESCAPE operator onto your query, and add a \ character before the value you want to escape.

SELECT * FROM tablename WHERE fieldname LIKE ‘%100\%%’ ESCAPE ‘\’

The ESCAPE ‘\’ part of the query tells the SQL engine to interpret the character after the \ as a literal character instead of as a wildcard.

Personally I find the second method easier to deal with, and you can use it to escape a square bracket as well.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 11/8/07

Comments (9)

  1. Whatever-ishere

    thanks for the GREAT post! Very useful…

  2. Ashutosh Mahamia

    This write up was extremely beneficial for me . Thanks for writing it. Keep it up.

  3. Weeha

    Yes but how do you search for all words that contain ‘

    I have tried a lot and cannot find this answer.

  4. Ken Nordeen

    Weeha, to search for any of the SQL “illegal” characters, use a combination of the instr and chr functions, in combination with the ASCII code table. For example, the ASCII code for “‘” is 39, so
    instr(, char(39)) > 1 will return all instances of which contain “‘”.

  5. Ken Nordeen

    in my previous post the parser stripped out some of my text. the last statement should read instr(string_being_searched, char(39)) > 1 will return all instances of string_being_searched which contain “‘”.

  6. Manikandan

    Good piece of information.. It helps me a lot in time being

  7. Todd

    Crazy useful. Thanks.

  8. Brett Sanders

    Thanks a lot! The ESCAPE qualifier doesn’t work in .NET framework datatable select statements, for example, doesnt work:
    Dim DT As New DataTable
    DT.Columns.Add(“test”)
    Dim DR As DataRow = DT.Rows.Add
    DR(“test”) = “brett’s test”
    Dim find() As DataRow = DT.Select(“test=’brett\’s test’ ESCAPE ‘\’”)
    Debug.Print(find.Length)
    Doesn’t work:
    Dim find() As DataRow = DT.Select(“test=’brett[']s test’”)
    Works (two single quotes):
    Dim find() As DataRow = DT.Select(“test=’brett”s test’”)

  9. Brett Sanders

    The brackets do work for net framework datatable select statements with LIKE where the string contains a %….
    “name LIKE ‘%” & replace(someString,”%”,”[%]“) & “%’”)

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!