在区分大小写的SQL Server数据库中执行不区分大小写的类似查询 [英] Perform a Case insensitive Like query in a case sensitive SQL Server database

问题描述

这是我的情况.

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

这篇关于在区分大小写的SQL Server数据库中执行不区分大小写的类似查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！