Transact-SQL collate doesn't work

Question

I'm trying to use PATINDEX function like this:

select PATINDEX('%[A-Z].%', 'he.llo MA. asd ' collate Cyrillic_General_CS_AS)

I expect it returns 9 but it returns 2. Can someone enlight me what's wrong? I also tried to supply collate in first parameter and use Latin1_General_CS_AS instead of Cyrrilic - the same result.

A duplicate? http://stackoverflow.com/questions/4212110/range-wildcard-pattern-matching-behaviour-with-case-sensitive-collations — dean, Apr 15 '14 at 16:00

score 1 · Accepted Answer · answered Apr 15 '14 at 16:21

1

Try forcing a BINARY comparison so it goes bit by bit.

This guys link was helpful in solving your problem. LINK

I duplicated the 2 you were getting and with the COLLATE Latin1_General_BIN it returned the expected 9.

SELECT PATINDEX('%[A-Z].%', 'he.llo MA. asd ' COLLATE Latin1_General_BIN )

answered Apr 15 '14 at 16:21

Matt

1,441
1
15
29

brilliant, it works, thanks! I'll also leave my workaround I found out based on reference to possible duplicate given in comments to my question – heximal Apr 15 '14 at 16:32
Glad it helped. Obviously change to any specific COLLATION you need just adding the BIN bit at the end if the Cryillic was what you were after. – Matt Apr 15 '14 at 16:33

score 1 · Answer 2 · edited May 23 '17 at 12:11

1

Here is my freaky workaround based on this SO thread (in case Matt Akers answer didn't help you):

select PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ].%', 'he.llo MA. asd ' collate Latin1_General_CS_AS)

edited May 23 '17 at 12:11

Community

1
1

answered Apr 15 '14 at 16:36

heximal

10,327
5
46
69

Transact-SQL collate doesn't work

2 Answers2