如何在 SQL Server 中使用 LIKE 运算符找到“%"? [英] How do I find ' % ' with the LIKE operator in SQL Server?

问题描述

我有一个列名地址，它由一些地址组成，中间有%":

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

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

等等等等

如何编写 LIKE 运算符来查找该模式?

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

我试过了:

declare @var char(1)
set @var='!%'
select Address from Accomodation where Address like '%'+@var+'%'

推荐答案

我会使用

WHERE columnName LIKE '%[%]%'

SQL Server 存储字符串摘要统计信息，用于估计与 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.

对 此连接项 状态

我们不支持精确的基数估计存在用户定义的转义字符.所以我们可能会变得很穷估计和一个糟糕的计划.我们将考虑在一个未来版本.

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.

一个例子

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 '\'

显示第一个查询的 457 个逻辑读取和第二个查询的 33,335 个.

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

这篇关于如何在 SQL Server 中使用 LIKE 运算符找到“%"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！