38

I have a column name Address which consists of some address which has '%' in between as:

Address
--------------------
Aman Ja%lan%
Stree% Ro%ad

etc., etc.

How I can write the LIKE operator to find that pattern?

I tried:

declare @var char(1)
set @var='!%'
select Address from Accomodation where Address like '%'+@var+'%'
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Teju MB
  • 1,333
  • 5
  • 20
  • 37

3 Answers3

55

I would use

WHERE columnName LIKE '%[%]%'

SQL Server stores string summary statistics for use in estimating the number of rows that will match a LIKE clause. The cardinality estimates can be better and lead to a more appropriate plan when the square bracket syntax is used.

The response to this Connect Item states

We do not have support for precise cardinality estimation in the presence of user defined escape characters. So we probably get a poor estimate and a poor plan. We'll consider addressing this issue in a future release.

An example 

CREATE TABLE T
(
X VARCHAR(50),
Y CHAR(2000) NULL
)

CREATE NONCLUSTERED INDEX IX ON T(X)

INSERT INTO T (X)
SELECT TOP (5) '10% off'
FROM master..spt_values
UNION ALL
SELECT  TOP (100000)  'blah'
FROM master..spt_values v1,  master..spt_values v2


SET STATISTICS IO ON;
SELECT *
FROM T 
WHERE X LIKE '%[%]%'

SELECT *
FROM T
WHERE X LIKE '%\%%' ESCAPE '\'

Shows 457 logical reads for the first query and 33,335 for the second.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Indeed martin, very lucid explanation, but what about the case when one has to search ']' ??? For example `INSERT INTO T (X) SELECT TOP (5) '10[] off' FROM master..spt_values` – Teju MB Sep 10 '13 at 03:00
  • Indeed martin, very lucid explanation, but what about the case when one has to search ']' ??? For example `INSERT INTO T (X) SELECT TOP (5) '10[] off' FROM master..spt_values` For this when i try to search with [], it did not come `SET STATISTICS IO ON; SELECT * FROM T WHERE X LIKE '%[]]%'` But it worked with the below one `SELECT * FROM T WHERE X LIKE '%\]%' ESCAPE '\'` – Teju MB Sep 10 '13 at 03:13
  • 1
    @TejuMB - That doesn't need escaping. Just use LIKE `'%]%'`. If you do `LIKE '%[AB]]%'` that means match a character in the set `A,B` then a `]`. If you do `LIKE '%[A]]%'` that means match `A]`. If you do `LIKE '%[]]%'` then it means "match a single character in this empty set of characters followed by a `]`. As the set is empty no character can ever match. – Martin Smith Sep 10 '13 at 07:54
20

You can use ESCAPE:

WHERE columnName LIKE '%\%%' ESCAPE '\'
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Thanks , actually i removed ESCAPE as i thought it may be for my reference. – Teju MB Sep 09 '13 at 07:34
  • 3
    Using LIKE '%[%]%' should the make trick too. – gotqn Sep 09 '13 at 07:38
  • 1
    @491243 Actually, this is pretty good point. You are not able to escape ']' like this '[]]', but you can escape it using the the ESCAPE keyword and escape character. +1 for this and it is a pity there is no ESCAPE keyword for PATHINDEX too. – gotqn Sep 09 '13 at 07:56
  • @gotqn - Why do you say you can't escape it like that? `SELECT 1 WHERE 'A[B' LIKE 'A[[]B'` works fine. Using `ESCAPE` can give worse plans as my answer shows. – Martin Smith Sep 09 '13 at 10:57
  • @MartinSmith You are right. I was trying to say that you can not escape "close" bracket, but it seems that it is not special symbol and does not need to be escaped. – gotqn Sep 09 '13 at 12:14
2

Try this:

declare @var char(3)
set @var='[%]'
select Address from Accomodation where Address like '%'+@var+'%'

You must use [] cancels the effect of wildcard, so you read % as a normal character, idem about character _

Joe Taras
  • 15,166
  • 7
  • 42
  • 55