CODEWITHSUNDEEP
sql-server
ca9163d9
ca9163d9

Reputation: 29159

like '%[a-z]%' collate SQL_Latin1_General_CP1_CS_AS still return upper case?

Running

SELECT SERVERPROPERTY('COLLATION');

returns SQL_Latin1_General_CP1_CI_AS.

However, both the following SQL returns ABC. Shouldn't they return no row?

with t as (select 'ABC' X)
select *
from t 
where X like '%[a-z]%' collate SQL_Latin1_General_CP1_CS_AS;

with t as (select 'ABC' X)
select *
from t 
where X collate SQL_Latin1_General_CP1_CS_AS like '%[a-z]%' collate SQL_Latin1_General_CP1_CS_AS

Upvotes: 2

Views: 350

Answers (1)

S3S
S3S

Reputation: 25112

This is a double headed issue of the collation with ranges using LIKE

This fails to return the value

create table t (x varchar(4))
insert into t values ('ABC')


select *
from t 
where X collate Latin1_General_CS_AS like '%a%' collate Latin1_General_CS_AS;

But this will

select *
from t 
where X collate Latin1_General_CS_AS like '%a%' collate Latin1_General_CS_AS;

In range searches as you have...

The characters included in the range may vary depending on the sorting rules of the collation

So, this collation and how it's handling the range which, I admit, isn't what I'd expect. If you list the values explicitly, as in all letters, it works as expected.

To fix this, as Sean stated in the comments, you need to use Latin1_General_BIN

Upvotes: 3

Related Questions