SQL Server-在设置COLLATE Latin1_General_CS_AS的位置搜索不区分大小写 [英] SQL Server - To search case insensitive where the COLLATE Latin1_General_CS_AS is set

问题描述

父母问题-感谢Iamdave，部分问题已解决。现在的挑战是，在已经设置以下排序规则的数据库中使搜索不区分大小写：COLLATE Latin1_General_CS_AS

Parent question - Thanks to Iamdave, part of the problem is solved. Now the challenge is to make the search case insensitive in the db where the following collation is set already: COLLATE Latin1_General_CS_AS

我正在使用此查询，但该查询无法正常工作-无法't match test，测试，只能匹配TEST

I am using this query and it is not working - couldn't match test, Test, could match only TEST

UPDATE dbo.BODYCONTENT 
SET BODY = LTRIM(RTRIM(REPLACE(
                          REPLACE(
                                REPLACE(N' ' + CAST(BODY AS NVARCHAR(MAX))
      + N' ', ' ', '<>'), '>TEST<', '>Prod<'), '<>', ' '))) 
FROM dbo.BODYCONTENT 
WHERE BODY COLLATE Latin1_General_CI_AS LIKE '%TEST%' COLLATE Latin1_General_CI_AS;  

如何使replace函数中的搜索字符串不区分大小写

How to make the search string in the replace function to match case insensitive

其他查询和结果：

    UPDATE dbo.BODYCONTENT SET BODY = 
    ltrim(rtrim(replace(replace(
    replace(N' ' + cast(BODY as nvarchar(max)) + N' ' ,' ','<>')                           
     ,'>Test<','>Prod<),'<>',' ')))
    from dbo.BODYCONTENT WHERE lower(BODY) like '%test%';

结果：参数数据类型ntext对于下级函数的参数1无效。

result: Argument data type ntext is invalid for argument 1 of lower function.

推荐答案

基于注释，使用 LOWER

where lower(body) like '%test%'

这篇关于SQL Server-在设置COLLATE Latin1_General_CS_AS的位置搜索不区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！