Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I find ' % ' with the LIKE operator in SQL Server? [duplicate]

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+'%' 
like image 345
Teju MB Avatar asked Sep 09 '13 07:09

Teju MB


People also ask

How do I find with the LIKE operator in SQL Server?

The SQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

What is a common way in SQL to identify duplicate records?

You can find duplicates by grouping rows, using the COUNT aggregate function, and specifying a HAVING clause with which to filter rows.

How do I find duplicate names in SQL?

To find the duplicate Names in the table, we have to follow these steps: Defining the criteria: At first, you need to define the criteria for finding the duplicate Names. You might want to search in a single column or more than that. Write the query: Then simply write the query to find the duplicate Names.


2 Answers

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.

like image 142
Martin Smith Avatar answered Sep 20 '22 14:09

Martin Smith


You can use ESCAPE:

WHERE columnName LIKE '%\%%' ESCAPE '\' 
  • SQLFiddle Demo
like image 36
John Woo Avatar answered Sep 21 '22 14:09

John Woo