CODEWITHSUNDEEP
sql-servercollationsql-like
user1035827
user1035827

Reputation: 13

Strange query results on different collation

During development I came across a strange scenario using DD with Macedonian_FYROM_90_CI_AS Collation.

declare @test_table  table
( numbers varchar(20) collate Macedonian_FYROM_90_CI_AS )

insert into @test_table values('0711110000000') 
insert into @test_table values('0711110000001')
insert into @test_table values('0711110000002')

select * from @test_table WHERE numbers like '071111%00000'

It should return 0711110000000, but result from query is null.

I try the same test on another DB using SQL_Latin1_General_CP1_CI_AS Collation and query returns 0711110000000.

Have anyone faced with the same problem, or can explain this?

Upvotes: 1

Views: 1569

Answers (2)

Damir Arh
Damir Arh

Reputation: 17855

This is strange behavior indeed. I've noticed it with other collations as well (Slovenian_100_CI_AS, Macedonian_FYROM_100_CI_AS, Finnish_Swedish_100_CI_AS ...) though some work as expected (Slovenian_CI_AS) on the same test set.

I decided to submit feedback to Microsoft Connect.

Upvotes: 1

Royi Namir
Royi Namir

Reputation: 148564

try : 

select * from test_table WHERE numbers like '071111%00000'  COLLATE SQL_Latin1_General_Cp1_CS_AS

Upvotes: 0

Related Questions