SQL区分大小写比较

Question

我想通过CASE语句了解两个字段是否相同。

我设法用下面的查询做到了，但我想知道是否有更好的方法？我只是想让'='做一个区分大小写的比较。我尝试过COLLATE，但后来我需要使用一个WHERE子句来过滤我的结果，我想要所有的行。使用HASHYTES似乎有些过头了，尤其是当我需要将其与ISNULL 结合时

DECLARE @myTable AS TABLE (old varchar(255), new varchar(255));
INSERT INTO @myTable VALUES
('test', 'test'),
('test', 'TEST'),
(null, null)

SELECT old, new, 
CASE WHEN HASHBYTES('SHA2_512', old) = HASHBYTES('SHA2_512', new) THEN 'same' ELSE 'changed' END AS updated,
CASE WHEN HASHBYTES('SHA2_512', ISNULL(old, '')) = HASHBYTES('SHA2_512', ISNULL(new, '')) THEN 'same' ELSE 'changed' END AS updated_isnull
FROM @myTable
--where old = new COLLATE Latin1_General_CS_AS

我需要列"updated_isnull">

| old  | new  | updated | updated_isnull |
| ---- | ---- | ------- | -------------- |
| test | test | same    | same           |
| test | TEST | changed | changed        |
| NULL | NULL | changed | same           |

Answer 1

正确的解决方案是修复列，以便将数据存储在区分大小写的排序规则中。然后你只需要做：

select old, new,
case when old = new
or (old is null and new is null)
then 1
else 0 
end as is_same
from @mytable

使用不区分大小写的排序规则，我们可以像这样处理

select old, new,
case when old collate Latin1_General_Bin = new collate Latin1_General_Bin 
or (old is null and new is null)
then 1
else 0 
end as is_same
from @mytable

DB Fiddle上的演示：

old|new|is_same：---|：---|------：测试|测试|1测试|test|0null|null|1

Answer 2

试试这个：

DECLARE @myTable AS TABLE (old varchar(255), new varchar(255));
INSERT INTO @myTable VALUES
('test', 'test'),
('test', 'TEST'),
(null, null)

SELECT old, new,
CASE WHEN old COLLATE Latin1_General_CS_AS = new COLLATE Latin1_General_CS_AS THEN 'same' ELSE 'changed' END AS updated,
CASE WHEN isnull(old,0) COLLATE Latin1_General_CS_AS = isnull(new,0) COLLATE Latin1_General_CS_AS THEN 'same' ELSE 'changed' END AS updated_isnull
FROM @myTable