如果列数据以数字开头，则 Like 操作在 nvarchar 列过滤器上不返回任何行

问题描述

我在 SQL Server 表中有 nvarchar(50) 列和如下数据:

I have nvarchar(50) column in SQL Server table and data like this:

123abc
234abc
456abc

我的查询:

select * 
from table 
where col like '%abc'

预期结果:应返回所有行实际结果:没有返回任何行

Expected result : all rows should be returnedActual result: No rows are returned

如果列是 varchar 可以正常工作，但如果类型是 nvarchar 则不返回任何行.

Works fine if the column is varchar but returns no rows if the type is nvarchar.

有什么想法吗?

推荐答案

您的数据末尾可能有空格.看看这个例子.

You probably have spaces at the end of your data. Take a look at this example.

Declare @Temp Table(col nvarchar(50))

Insert Into @Temp(col) Values(N'123abc')
Insert Into @Temp(col) Values(N'456abc ')

Select * From @Temp Where Col Like '%abc'

当你运行上面的代码时，你只会得到 123 行，因为 456 行在它的末尾有一个空格.

When you run the code above, you will only get the 123 row because the 456 row has a space on the end of it.

当您运行如下所示的代码时，您将获得您期望的数据.

When you run the code shown below, you will get the data you expect.

Declare @Temp Table(col nvarchar(50))

Insert Into @Temp(col) Values(N'123abc')
Insert Into @Temp(col) Values(N'456abc ')

Select * From @Temp Where rtrim(Col) Like '%abc'

根据在线书籍中关于 LIKE 的文档(重点是我的):http://msdn.microsoft.com/en-us/library/ms179859.aspx

According to the documentation regarding LIKE in books on line (emphasis mine):http://msdn.microsoft.com/en-us/library/ms179859.aspx

使用 LIKE 进行模式匹配

Pattern Matching by Using LIKE

LIKE 支持 ASCII 模式匹配和 Unicode 模式匹配.当所有参数(match_expression、pattern 和 escape_character，如果存在)都是 ASCII 字符数据类型时，将执行 ASCII 模式匹配.如果任一参数是 Unicode 数据类型，则所有参数都将转换为 Unicode 并执行 Unicode 模式匹配.当您将 Unicode 数据(nchar 或 nvarchar 数据类型)与 LIKE 一起使用时，尾随空白很重要；但是，对于非 Unicode 数据，尾随空白并不重要.Unicode LIKE 与 ISO 标准兼容.ASCII LIKE 与早期版本的 SQL Server 兼容.

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any one of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the ISO standard. ASCII LIKE is compatible with earlier versions of SQL Server.

这篇关于如果列数据以数字开头，则 Like 操作在 nvarchar 列过滤器上不返回任何行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！