2

I have a function (which I found here years ago) that uses STUFF / PATINDEX to strip out non-alphanumeric characters from a string. When running on a Case Insensitive collation, it works fine. Recently I needed to use it on a Case Sensitive collation DB and found some odd behaviors. If the %pattern% for PATINDEX is just specifying lower case (ex: %[^a-z0-9_-]% ), then all upper case "Z"s are removed when the Collation is Latin1_General_100_CS_AS. If the Collation is SQL_Latin1_General_CP1_CS_AS, then upper case "A"s are removed. Is this a bug or did I miss something?

USE TestCollation
GO
PRINT '---------SourceString---------'
PRINT 'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
GO
ALTER DATABASE TestCollation COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
PRINT '---------SQL_Latin1_General_CP1_CI_AS---------'
GO

DECLARE @ExternalId VARCHAR(255) =
     'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
DECLARE @return VARCHAR(255)
SET @return = @ExternalId
DECLARE @KeepValues AS VARCHAR(50)
SET @KeepValues = '%[^a-z0-9_-]%'
WHILE PATINDEX ( @KeepValues, @return ) > 0
BEGIN
    SET @return = STUFF ( @return, PATINDEX ( @KeepValues, @return ), 1, '' )
END
PRINT @return

go

ALTER DATABASE TestCollation COLLATE Latin1_General_100_CS_AS;
GO
PRINT '---------Latin1_General_100_CS_AS---------'
GO
DECLARE @ExternalId VARCHAR(255) =
     'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
DECLARE @return VARCHAR(255)
SET @return = @ExternalId
DECLARE @KeepValues AS VARCHAR(50)
SET @KeepValues = '%[^a-z0-9_-]%'
WHILE PATINDEX ( @KeepValues, @return ) > 0
BEGIN
    SET @return = STUFF ( @return, PATINDEX ( @KeepValues, @return ), 1, '' )
END
PRINT @return

ALTER DATABASE TestCollation COLLATE SQL_Latin1_General_CP1_CS_AS;
GO
PRINT '---------SQL_Latin1_General_CP1_CS_AS---------'
GO
DECLARE @ExternalId VARCHAR(255) =
     'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
DECLARE @return VARCHAR(255)
SET @return = @ExternalId
DECLARE @KeepValues AS VARCHAR(50)
SET @KeepValues = '%[^a-z0-9_-]%'
WHILE PATINDEX ( @KeepValues, @return ) > 0
BEGIN
    SET @return = STUFF ( @return, PATINDEX ( @KeepValues, @return ), 1, '' )
END
PRINT @return

1 Answer 1

4

This is not a bug. It is merely a difference in which case comes first when doing case-sensitive sorting. While it might not appear that any sorting is being done, the [...] character range wildcard used in both LIKE and PATINDEX does, in a sense, sort characters when applying a range, such as any {character}-{character} pattern (in this case a-z and 0-9). So, the two options are:

  1. AaBbCc...Zz (a-z excludes A)
  2. aAbBcC...zZ (a-z excludes Z)

The SQL Server collations (i.e. those having names starting with SQL_) mostly use one approach while the Windows collations (i.e. those having names not starting with SQL_) use the other approach.

To illustrate the behavior:

SELECT * FROM (VALUES ('A'), ('a'), ('Z'), ('z')) tmp (val)
WHERE tmp.val LIKE '%[a-z]%' COLLATE SQL_Latin1_General_CP1_CS_AS
ORDER BY tmp.val COLLATE SQL_Latin1_General_CP1_CS_AS;
/*
a
Z
z
*/

SELECT * FROM (VALUES ('A'), ('a'), ('Z'), ('z')) tmp (val)
WHERE tmp.val LIKE '%[a-z]%' COLLATE Latin1_General_100_CS_AS
ORDER BY tmp.val COLLATE Latin1_General_100_CS_AS;
/*
a
A
z
*/

SELECT * FROM (VALUES ('A'), ('a'), ('Z'), ('z')) tmp (val)
WHERE tmp.val LIKE '%[a-z]%' COLLATE Latin1_General_100_BIN2
ORDER BY tmp.val COLLATE Latin1_General_100_BIN2;
/*
a
z
*/

FYI: You can avoid having to deal with the database's default collation by either forcing a collation in the two calls to PATINDEX:

GO
DECLARE @ExternalId VARCHAR(255) =
    'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
DECLARE @return VARCHAR(255)
SET @return = @ExternalId
DECLARE @KeepValues AS VARCHAR(50)
SET @KeepValues = '%[^a-z0-9_-]%'
WHILE PATINDEX ( @KeepValues COLLATE Latin1_General_100_CI_AS, @return ) > 0
BEGIN
    SET @return = STUFF ( @return,
                          PATINDEX ( @KeepValues COLLATE Latin1_General_100_CI_AS,
                                     @return ),
                          1,
                          '' )
END
PRINT @return
GO

or, adding A-Z to the pattern:

SET @KeepValues = '%[^a-zA-Z0-9_-]%'
1
  • Thank you for the explanation. My fix was to add the A-Z to the pattern.
    – Jay Austin
    Jun 28 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.