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

本文介绍了在区分大小写的SQL Server数据库中执行不区分大小写的类似查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我的情况.

SQL Server 2014 Standard Edition，我有一个排序规则为 SQL_Latin1_General_CP437_BIN2 的数据库，该数据库区分大小写.

SQL Server 2014 Standard edition, I have a database with a collation SQL_Latin1_General_CP437_BIN2 which is case sensitive.

我想要执行一个 LIKE 查询，无论大小写敏感，该查询都应返回输出.

I want to perform a LIKE query which should return the output irrespective of case sensitive.

例如:如果我执行 Like 查询以获取用户名'John'的记录，则无论大小写区分'JOHN'，'John'，'john'，'joHN，它也应返回行'.

Ex: if i execute a Like query to fetch the records with userName 'John' it should also return rows irrespective of case sensitive 'JOHN', 'John', 'john','joHN'.

我尝试使用 Lcase ， Ucase ，但出现错误

I tried using Lcase, Ucase, but I am getting the error

第155条消息的状态15，状态10，第4行"LCASE"不是公认的内置函数名称.

Msg 195, Level 15, State 10, Line 4 'LCASE' is not a recognized built-in function name.

这是我的示例查询

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

请大家帮我解决您的宝贵建议，我对使用基于排序规则的数据库感到困惑.

Guys help me out with your valuable suggestions , I am bit confused of using collation based DB.

推荐答案

您可以使用 UPPER 或 LOWER 函数将值转换为相同的大小写.例如:

You can use UPPER or LOWER functions to convert the values to the same case. For example:

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

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

Alternatively, you can specify the collation manually when comparing:

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