Problem with PATINDEX function

Hi all,

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

SELECTpatindex('%[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

Hi,

You can do this in two ways.

1) You can permamnetly change the case sensitivity settings for your database. Assuming you have SQL 2000 the following command will work for you

ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS

Run you query after this and it will perform case sensitive searches.

2) Use Collation key word. Change you query to

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

Collate SQL_Latin1_General_CP1_CS_AS stands forLatin1-General,case-sensitive, accent-sensitive

|||

Hi Girish,

Thanks a lot. It works fine.

Regards,

Gaurang Majithiya