这是我的情况.
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数据库中执行不区分大小写的类似查询
发布评论