如何使用 LIKE 执行区分大小写的搜索?

问题描述

我正在尝试查找包含 6 个或更多大写字母数字字符的字符串的记录.一些例子:

I'm trying to find records that contain a string of 6 or more alpha-numeric characters in uppercase. Some examples:

PENDING  3RDPARTY  CODE27

我正在使用以下语句:

SELECT Details
FROM MyTable
WHERE Details LIKE '%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%';

这将返回包含任何 6 个或更多字母的单词的所有记录，无论大小写.

This is returning all records that contain any 6-or-more-letter word, regardless of case.

我添加了一个 COLLATE 语句:

I've added a COLLATE statement:

SELECT Details
FROM MyTable
WHERE Details COLLATE Latin1_General_CS_AS LIKE '%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%';

这没有任何改变.无论大小写，它仍会返回包含 6 个或更多字母的单词的记录.

This changes nothing. It still returns records with 6-or-more-letter word, regardless of case.

作为测试，我尝试了:

SELECT Details
FROM MyTable
WHERE Details COLLATE Latin1_General_CS_AS LIKE '%pending%';

SELECT Details
FROM MyTable
WHERE Details COLLATE Latin1_General_CS_AS LIKE '%PENDING%';

这两个都有效，分别返回包含pending"和PENDING"的记录.所以这个问题似乎出自 LIKE 子句的模式匹配.

Both of these worked, returning records containing "pending" and "PENDING" respectively. So the issue seems to by the LIKE claus's pattern matching.

我该怎么做才能执行这种区分大小写的搜索?

What can I do to perform this case-sensitive search?

推荐答案

尝试使用 COLLATE Latin1_General_BIN 而不是 COLLATE Latin1_General_CS_AS

Try using COLLATE Latin1_General_BIN rather than COLLATE Latin1_General_CS_AS

这篇关于如何使用 LIKE 执行区分大小写的搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！