SQL Server Like 查询不区分大小写

Question

查询

SELECT  * 
from Table_2  
WHERE  name like ('Joe');

输出

1   100 Joe2   200 JOE3   300 jOE4   400 joe

为什么不区分大小写？

Answer 1

问题:

Query not case sensitive

原因:“名称”列的排序规则不区分大小写 (CI)。

解决方案:您必须使用 CS 排序规则:SELECT * FROM fn_helpcollat​​ions() WHERE description LIKE N'%case-sensitive%'。

注意:有数据库排序规则和列级排序规则。而且，还有一个服务器级排序规则。

SELECT  DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation
/*
-- Sample output (my database)
DatabaseCollation
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

SELECT  col.collation_name AS ColumnCollation
FROM    sys.columns col
WHERE   col.object_id = OBJECT_ID(N'dbo.Table_2') 
AND     col.name = N'Name'
/*
-- Sample output (my database)
ColumnCollation
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

简单地更改数据库排序规则不会更改现有用户表和列的排序规则:

This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.

Source

更改数据库排序规则后，上述查询的输出将是:

/*
DatabaseCollation -- changed
----------------------------
SQL_Latin1_General_CP1_CS_AS
*/

/*
ColumnCollation -- no change
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

而且，如您所见，Name 列的排序规则仍然是 CI。

此外，更改数据库排序规则只会影响新创建的表和列。因此，更改数据库排序规则可能会产生奇怪的结果(在我的观点中)，因为某些 [N][VAR]CHAR 列将是 CI 而新列将是 CS。

详细解决方案 #1:如果只是一些对 Name 列的查询需要是 CS 那么我将重写这些查询的 WHERE 子句:

SELECT  Name 
FROM    dbo.Table_2
WHERE   Name LIKE 'Joe' AND Name LIKE 'Joe' COLLATE SQL_Latin1_General_CP1_CS_AS

这将改变 SQL Server 以在 Name 列上执行 Index Seek(在 Name 列上有一个索引) .此外，执行计划将包括隐式转换(请参阅 Index Seek 的 Predicate 属性)，因为以下谓词 Name = N'Joe' COLLATE SQL_Latin1_General_CP1_CS_AS.

详细解决方案 #2:如果列 Name 的所有查询都需要是 CS，那么我将仅更改列 Name 的排序规则，因此:

-- Drop all objects that depends on this column (ex. indexes, constraints, defaults)
DROP INDEX IX_Table_2_Name ON dbo.Table_2

-- Change column's collation
ALTER TABLE dbo.Table_2
ALTER COLUMN Name VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS
-- Replace VARCHAR(50) with proper data type and max. length
-- Replace COLLATE SQL_Latin1_General_CP1_CS_AS with the right CS collation

-- Recreate all objects that depends on column Name (ex. indexes, constraints, defaults)
CREATE INDEX IX_Table_2_Name ON dbo.Table_2 (Name)

-- Test query
SELECT  Name 
FROM    dbo.Table_2
WHERE   Name LIKE 'Joe'