如何使用 collate 在嵌套大小写语句中进行区分大小写的搜索

Question

>我目前正在运行一个查询，其中我正在查找列中区分大小写的字母"%AR%"。还有其他单词的名称中包含字母，有时字母与另一个单词配对，例如"ARmart"，因此区分我正在寻找的内容的唯一方法是按大小写。这是我需要在 case when end_time is not null then 'completed' when status='S' or status='SR' then 'In Progress' else 'not completed' end as completed, case when status != 'D' then 'Error Occured' end as error,case when service like '%AR%' then 'Accounts Receivable' end as used_by中使用它的案例陈述。

Answer 1

默认情况下，

您的like将返回带有AR的任何内容。下面是一个示例：

declare @lower varchar (16), @mix varchar(16), @upper varchar(16)
set @lower = 'ddarcc'
set @mix = 'dDArcc'
set @upper = 'DDARCC'
select @lower where @lower like '%AR%'
select @mix where @mix like '%AR%'
select @upper where @upper like '%AR%'

要更改此设置，请将其整理为区分大小写：

declare @lower varchar (16), @mix varchar(16), @upper varchar(16)
set @lower = 'ddarcc'
set @mix = 'dDArcc'
set @upper = 'DDARCC'
select @lower where @lower  like '%AR%' COLLATE Latin1_General_CS_AS
select @mix Latin1_General_CS_AS where @mix like '%AR%' COLLATE Latin1_General_CS_AS
select @upper Latin1_General_CS_AS where @upper like '%AR%' COLLATE Latin1_General_CS_AS