2

I am using SQL Server 2012 and I need to perform a search on a specific field, called Notes. The search criteria is to find all rows where the term 8% is mentioned in that specific field.

The WHERE clause of my T-SQL query looks like this:

WHERE [Notes] like '%[8%]%'

However, the query is not filtering correctly based on the above syntax. It is also including rows where the term 8 is mentioned.

I had a look at the answers proposed in the question below, but they are still not giving me the correct answer.

SQL 'LIKE' query using '%' where the search criteria contains '%'

user3115933
  • 4,303
  • 15
  • 54
  • 94

3 Answers3

5

A single character class represents a single character. So [%] means a literal percent symbol, and 8[%] means literal 8%. Try this:

SELECT * FROM yourTable WHERE [Notes] like '%8[%]%'

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
3

you need to escape % in query for example below 

SELECT columns FROM table
   WHERE column LIKE '%\%%' ESCAPE '\'

Using SQL Escape Sequences

Below is from MSDN

Pattern Matching with the ESCAPE Clause

You can search for character strings that include one or more of the special wildcard characters. For example, the discounts table in a customers database may store discount values that include a percent sign (%). To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named comment that contains the text 30%. To search for any rows that contain the string 30% anywhere in the comment column, specify a WHERE clause such as WHERE comment LIKE '%30!%%' ESCAPE '!'. If ESCAPE and the escape character are not specified, the Database Engine returns any rows with the string 30.

you can try below answer given by @TimBiegeleisen that is also easy way.

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
0

just change your where clause as 

WHERE `Notes` LIKE '%8!%%' ESCAPE '!
M. Paul
  • 361
  • 5
  • 18