Search code examples
sql-servert-sqlcollatepatindex

Transact-SQL collate doesn't work

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.

Solution

  • 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 )