在区分大小写的SQL Server数据库中执行不区分大小写的Like查询

Question

这是我的情况。

SQL Server 2014标准版，我有一个使用大小写区分的排序规则SQL_Latin1_General_CP437_BIN2的数据库。

我想执行一个LIKE查询，无论大小写敏感性都应返回结果。

例如：如果我执行一个Like查询来获取用户名为“John”的记录，它也应该返回大小写不敏感的行：“JOHN”，“John”，“john”和“joHN”。

我尝试使用Lcase，Ucase，但我收到了错误消息。

这是我的样本查询。

SELECT TOP 300 * 
FROM
    (SELECT 
         userNo, userName, Place, Birthdate
     FROM usertable 
     WHERE personid = 2 
       AND (Name LIKE LCASE('%john%')) 

     UNION 

     SELECT 
         userNo, userName, Place, Birthdate, 
     FROM usertable2 
     WHERE personid = 2 
       AND (Name LIKE LCASE( '%john%') OR Place LIKE LCASE('%NY%')) ) a 
ORDER BY 
    userNo

各位，帮我提供一些宝贵的建议，我有点困惑于使用基于排序规则的数据库。

- Rajakrishnan

---

SQL Server的函数用于将字符值转换为大写或小写，分别称为“UPPER”和“LOWER”。（字符串函数列表。）你可能正在查找错误的关系数据库管理系统文档... - Matt Gibson

@Matt Gibson 感谢您指出我的错误，我应该使用 UPPER / LOWER 函数。 - Rajakrishnan

Answer 1

你可以使用 UPPER 或 LOWER 函数将值转换为相同的大小写。例如：

SELECT *
FROM YourTable
WHERE UPPER(YourColumn) = UPPER('VALUE')

或者，在进行比较时，您可以手动指定排序规则：

SELECT *
FROM YourTable
WHERE YourColumn = 'VALUE' COLLATE SQL_Latin1_General_CP1_CI_AI

- DavidG

---

想知道将列值转换为 UPPER 或 LOWER 大小写然后使用 LIKE 进行搜索是否会有任何性能问题？ - Shaiju T

当调用函数时，性能肯定会受到影响。不过你可以通过一个持久化的计算列来缓解这个问题。 - DavidG

@DavidG，谢谢，你能推荐一篇关于如何为不区分大小写的搜索做计算列的文章吗？ - Shaiju T

调用 UPPER/LOWER 应该会更慢，特别是对于许多行。不过你需要测试一下。这是一个简单的任务。此外，UPPER/LOWER 和更改 COLLATION 之间有区别。更改排序规则执行字符转换（重音不敏感，即 À 变成 A），而大小写转换则不会执行此操作！ - Paul-Sebastian Manole

@Paul-SebastianManole 嗯，这取决于您选择哪种排序规则。您可以选择区分大小写的排序规则，它会将 À 和 A 视为不同字符，或者选择不区分重音符号的排序规则。排序规则中的 _CI 和 _AI 部分告诉您它会执行什么操作。 - DavidG

@DavidG，我认为这是暗示，但确实如此。另外，我认为你的意思是“您可以选择不区分重音符号，这将使À和A有所不同，或者选择区分重音符号，这将不会有所不同。”而不是“您可以选择区分大小写，这将使À和A有所不同，或者选择区分重音符号，这将不会有所不同。”。 - Paul-Sebastian Manole

Answer 2

除了使用lower()，你还需要将其应用于列(column)而不是模式(pattern)。模式已经是小写字母形式。

Select  top 300 a.*
from (SELECT userNo, userName, Place, Birthdate
      FROM usertable 
      where personid = 2 and lower(Name) LIKE '%john%'
      UNION 
      SELECT userNo, userName, Place, Birthdate
      FROM usertable2 
      where personid = 2 and
            (lower(Name) like '%john%' or lower(Place) like '%ny%')
     ) a
order by userNo;

请注意，UNION ALL比UNION更可取，除非您有意要承担删除重复项的开销。

- Gordon Linoff

Answer 3

语法在不同的Db-Provider中有所不同，例如：

使用ODCB Provider for Microsoft时，“select * from TABLE1 where upper(COL) like 'SOMETHING'”会出现语法错误... 正确的语法是：“select * from TABLE1 where ucase(COL) like 'SOMETHING'”，即大写字母

因此，您应该使用您的数据库提供程序使用的正确语法

谢谢

- Ahmed Alshubaky

Answer 4

使用以下代码

SELECT TOP 300 * FROM (SELECT userNo, userName, Place, Birthdate FROM usertable WHERE personid = 2 AND (Name LIKE '%john%') UNION

 SELECT 
     userNo, userName, Place, Birthdate, 
 FROM usertable2 
 WHERE personid = 2 
   AND (Name LIKE '%john%' OR Place LIKE '%NY%') a 

按照 userNo 排序

- Dinesh Chaudhary