SQL Server Like Query 不区分大小写 [英] SQL Server Like Query not case sensitive

问题描述

查询

SELECT *来自表_2WHERE 名称如 ('Joe');

输出

<前>1 100 乔2 200 乔3 300 乔4 400 乔

为什么不区分大小写?

解决方案

问题:

<块引用>

查询不区分大小写

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

解决方案:您必须使用 CS 归类:SELECT * FROM fn_helpcollat​​ions() WHERE description LIKE N'%case-sensitive%'.

注意:有数据库整理和列级整理.而且，还有服务器级别的整理.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collat​​ion') AS DatabaseCollat​​ion/*-- 示例输出(我的数据库)数据库整理-----------------------------SQL_Latin1_General_CP1_CI_AS*/SELECT col.collat​​ion_name AS ColumnCollat​​ionFROM sys.columns 列WHERE col.object_id = OBJECT_ID(N'dbo.Table_2')AND col.name = N'Name'/*-- 示例输出(我的数据库)列排序规则-----------------------------SQL_Latin1_General_CP1_CI_AS*/

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

<块引用>

此语句不会更改任何列的排序规则现有的用户定义表.这些可以通过使用ALTER TABLE 的 COLLATE 子句.

来源

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

/*数据库整理 -- 已更改-----------------------------SQL_Latin1_General_CP1_CS_AS*//*ColumnCollat​​ion -- 没有变化-----------------------------SQL_Latin1_General_CP1_CI_AS*/

并且，正如您所看到的，Name 列的排序规则仍然是 CI.

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

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

选择名称从 dbo.Table_2WHERE Name LIKE 'Joe' AND Name LIKE 'Joe' COLLATE SQL_Latin1_General_CP1_CS_AS

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

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

-- 删除依赖于该列的所有对象(例如索引、约束、默认值)DROP INDEX IX_Table_2_Name ON dbo.Table_2-- 更改列的排序规则更改表 dbo.Table_2ALTER COLUMN Name VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS-- 用正确的数据类型和最大值替换 VARCHAR(50).长度-- 用正确的 CS 排序规则替换 COLLATE SQL_Latin1_General_CP1_CS_AS-- 重新创建依赖于列名称的所有对象(例如索引、约束、默认值)CREATE INDEX IX_Table_2_Name ON dbo.Table_2(名称)-- 测试查询选择名称从 dbo.Table_2WHERE Name LIKE 'Joe'

Query

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

Output

1   100 Joe
2   200 JOE
3   300 jOE
4   400 joe

Why is it not case sensitive?

解决方案

Problem:

Query not case sensitive

Cause: Column 'Name' has a case-insensitive (CI) collation.

Solution: You have to use a CS collation: SELECT * FROM fn_helpcollations() WHERE description LIKE N'%case-sensitive%'.

Note: There is a database collation and column level collation. And, there is, also, a server level collation.

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
*/

Simply changing database collation will NOT change the collation for existing user tables and columns:

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

After changing database collation, the output of above queries will be:

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

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

and, as you can see the collation of column Name remains CI.

More, changing database collation will affect only the new created tables and columns. Thus, changing database collation could generate strange results (in my opinion) because some [N][VAR]CHAR columns will be CI and the new columns will be CS.

Detailed solution #1: if just some queries for column Name need to be CS then I will rewrite WHERE clause of these queries thus:

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

This will give a change to SQL Server to do an Index Seek on column Name (in there is an index on column Name). Also, the execution plan will include an implicit conversion (see Predicate property for Index Seek) because of following predicate Name = N'Joe' COLLATE SQL_Latin1_General_CP1_CS_AS.

Detailed solution #2: if all queries for column Name need to be CS then I will change the collation only for column Name thus:

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

这篇关于SQL Server Like Query 不区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！