Understanding PATINDEX query

Question

I am new to SQL server 2000 and 2005. Can anyone tell me why I am getting wrong results while executing the following simple queries

Case 1

SELECT PATINDEX('%[áčďéěíňóřšťúůýžÁČĎÉĚÍŇÓŘŠŤÚŮÝŽ]%', 'This is a test')

Ans: 1

Case 2

SELECT PATINDEX('%[áčďéěíňóřšťúůýžÁČĎÉĚÍŇÓŘŠŤÚŮÝŽ]%', 'This is a teŠt')

Ans: 1

Case 3

SELECT PATINDEX('%[áčďéěíňóřšťúůýžÁČĎÉĚÍŇÓŘŠŤÚŮÝŽ]%', 'his is a teŠt')

Ans: 10 (Deleted "T" from the word "This" and got the Correct answer)

Answer 1

0

You need to specify the strings as unicode strings, and use a collation that is at least case and accent sensitive. I have chosen the most sensitive Latin1 code page available for this query:

SELECT PATINDEX(N'%[áčďéěíňóřšťúůýžÁČĎÉĚÍŇÓŘŠŤÚŮÝŽ]%', N'This is a test' COLLATE Latin1_General_100_CS_AS_KS_WS)
UNION ALL
SELECT PATINDEX(N'%[áčďéěíňóřšťúůýžÁČĎÉĚÍŇÓŘŠŤÚŮÝŽ]%', N'This is a teŠt' COLLATE Latin1_General_100_CS_AS_KS_WS)
UNION ALL
SELECT PATINDEX(N'%[áčďéěíňóřšťúůýžÁČĎÉĚÍŇÓŘŠŤÚŮÝŽ]%', N'his is a teŠt' COLLATE Latin1_General_100_CS_AS_KS_WS)

The results from this query are:

0
13
12

Also, note that your case 3 answer was not correct - it was returning the index of the first 't'.

answered Nov 09 '09 at 09:45 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

Hi Matt, Thank a lot for your Ans. It helped me a lot. It worked fine without COLLATE Latin1_General_100_CS_AS_KS_WS). But With COLLATE Latin1_General_100_CS_AS_KS_WS) i am getting invalid collation error. Please help I am on SQL server 2005. With regards Novice

Nov 10 '09 at 12:19 AM SQLserver_novice

On SQL Server 2005 you would most likely want to use the collation Latin1_General_CS_AS_KS_WS... You can always get a list of collations by using SELECT * FROM ::fn_helpcollations()

Nov 10 '09 at 12:50 PM Matt Whitfield ♦♦

(comments are locked)

Understanding PATINDEX query

Follow this question

Related Questions