SQL Server - To search case insensitive where the COLLATE Latin1_General_CS_AS is set

Question

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

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;

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

Other queries and results:

    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%';

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

so your where clause is causing the issue? You want it to only match upper case TEST? — S3S, Jun 22 '18 at 18:09
No, my where clause is not causing the issue...I want to match all cases of 'TEST' - test, Test, TEST.... But currently it is matching only 'TEST' — Ilak, Jun 22 '18 at 18:22
Could you confirm, it's the REPLACE that is not matching different cases of 'TEST'? — JohnRC, Jun 22 '18 at 22:38
Have you tried putting the ` COLLATE Latin1_General_CI_AS ` just after the ` CAST(BODY ... ) ` so that the collation applies within the REPLACE statement? — JohnRC, Jun 22 '18 at 22:47

score 1 · Answer 1 · answered Jun 22 '18 at 18:25

1

Based on the comments, it'd be easier to just use LOWER

where lower(body) like '%test%'

answered Jun 22 '18 at 18:25

S3S

24,809
5
26
45

score 0 · Answer 2 · answered Jun 22 '18 at 19:01

What you have there should work, unless there's some assumption that's being left out of the question (such as not actually being collated like you think, or the test rows actually being absent.

You can do this a couple ways. As scsimon pointed out, you could simply do a lower case comparison. That's probably the most straight forward.

You can also explicitly collate the column like you're doing. You shouldn't need to specifically collate the '%TEST%' string though (unless I'm mistaken; on my machine it wasn't necessary. I suppose default DB settings might negate this argument).

Finally, another option is to have a computed column on the table which is the case insensitive version of the field. That's essentially the same as the previous method, but it's part of the table definition instead.

declare @t table
(
    body nvarchar(max) collate Latin1_General_CS_AS,
    body_Insensitive as body collate Latin1_General_CI_AS
)

insert into @t
values ('test'), ('Test'), ('TEST')

select * from @t where BODY collate Latin1_General_CI_AS like '%test%' collate Latin1_General_CI_AS;

select * from @t where lower(body) like '%test%'

select * from @T where body_Insensitive like '%TeSt%'

SQL Server - To search case insensitive where the COLLATE Latin1_General_CS_AS is set

2 Answers2

Linked