7

Using PATINDEX and a case-sensitive collation to search for upper-case letters in a string I noticed this was not yielding the desired result:

-- returns 1
SELECT PATINDEX('%[A-Z]%'
                    , 'abCde' COLLATE SQL_Latin1_General_Cp1_CS_AS);

however, specifying every letter, A-Z, does:

-- returns 3
SELECT PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
                    , 'abCde' COLLATE SQL_Latin1_General_Cp1_CS_AS);

Is my understanding of using a range in the first case incorrect? Why is the behaviour like this?

Pero P.
  • 25,813
  • 9
  • 61
  • 85
  • Thanks for this, I needed to extract rows starting with upper case letters only: PATINDEX('[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', [NAME] COLLATE SQL_Latin1_General_Cp1_CS_AS) = 1 – David Clarke May 13 '11 at 03:23

1 Answers1

6

Unfortunately, the range operators are a bit funny. The range of letters from A-Z is:

AbBcCdDeE...yYzZ

That is, lower case characters immediately precede their upper case counterpart. This is also fun because if you want to deal with both upper and lower case characters, in a case sensitive collation, the range A-Z excludes lower case a.

I should say the above, regarding how the range expands out, is based on the collations I generally work with. How the range actually expands is collation dependent. If you can find a collation where, for instance, all upper case characters occur before all lower case characters, then the range would work as you expect. (Possibly one of the binary collations?)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 1
    Spot on @Damien_The_Believer! Switching to Latin1_General_100_BIN2 yields the same result, 3, for both of the snippets in my question above.. thanks for the clarification – Pero P. Nov 18 '10 at 07:53
  • This article may also be helpful: https://social.technet.microsoft.com/wiki/contents/articles/15307.patindex-case-sensitive-search.aspx – user2905353 Mar 30 '20 at 23:43