Problem with PATINDEX function for case-sensitive information

Hi,

My database is not case-sensitive, but I want output like...

SELECT patindex('%[A-Z]%','gaurang Ahmedabad')

The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.

Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).

Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?

Thanks,

Gaurang Majithiya

The default collation “SQL_Latin1_General_CP1_CI_AS” is case insensitive - CI stands for case insensitive, change the collation as “SQL_Latin1_General_Cp1_CS_AS” – here CS means case sensitive.

So the final query is,

SELECT patindex('%[A-Z]%','gaurang Ahmedabad' COLLATE SQL_Latin1_General_Cp1_CS_AS)

|||

Thanks for your reply, but still this will not work.

It will work like this, as I got reply in another forum forums.asp.net.

SELECT patindex('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%','gaurang Ahmedabad' COLLATE SQL_Latin1_General_CP1_CS_AS)

Thanks,

Gaurang.