Unexpected LIKE behavior

[Dimitri Furman]

SQL Server 2000 SP4.

Running the script below prints 'Unexpected':

-----------------------------
DECLARE @String AS varchar(1)

SELECT @String = 'z'

IF @String LIKE '[' + CHAR(32) + '-' + CHAR(255) + ']'
PRINT 'Expected'
ELSE
PRINT 'Unexpected'
-----------------------------

If the @String variable is set to 'y' (or in fact any ANSI character other
than 'z'), the result is 'Expected'. The comparison also evaluates as
expected if CHAR(255) is replaced with CHAR(254). The server collation, if
that matters, is SQL_Latin1_Gene ral_CP1_CI_AS.

It would be helpful to find the explanatin of this behavior. Thanks.

--
(remove a 9 to reply by email)

[Ed Murphy]

Dimitri Furman wrote:

SQL Server 2000 SP4.

Running the script below prints 'Unexpected':

-----------------------------
DECLARE @String AS varchar(1)

SELECT @String = 'z'

IF @String LIKE '[' + CHAR(32) + '-' + CHAR(255) + ']'
PRINT 'Expected'
ELSE
PRINT 'Unexpected'
-----------------------------

If the @String variable is set to 'y' (or in fact any ANSI character other
than 'z'), the result is 'Expected'. The comparison also evaluates as
expected if CHAR(255) is replaced with CHAR(254). The server collation, if
that matters, is SQL_Latin1_Gene ral_CP1_CI_AS.

It would be helpful to find the explanatin of this behavior. Thanks.

CHAR(255) = 'y' with an umlaut
http://englishplus.com/xascii.htm

[Erland Sommarskog]

Dimitri Furman (df*****@cloud9 9.net) writes:

SQL Server 2000 SP4.

Running the script below prints 'Unexpected':

-----------------------------
DECLARE @String AS varchar(1)

SELECT @String = 'z'

IF @String LIKE '[' + CHAR(32) + '-' + CHAR(255) + ']'
PRINT 'Expected'
ELSE
PRINT 'Unexpected'
-----------------------------

If the @String variable is set to 'y' (or in fact any ANSI character other
than 'z'), the result is 'Expected'. The comparison also evaluates as
expected if CHAR(255) is replaced with CHAR(254). The server collation, if
that matters, is SQL_Latin1_Gene ral_CP1_CI_AS.

It would be helpful to find the explanatin of this behavior. Thanks.

I suspect that this is a case of wrong expectations. When you have a
range in a pattern, it is not defined from ASCII codes, but from the
collation. Only if the collation is a binary collation, character codes
apply. As Ed said char(255) is ÿ, so z is definitely outside the range.

This prints "Expected":

DECLARE @String AS varchar(1)

SELECT @String = 'z'

IF @String COLLATE Latin1_General_ BIN
LIKE '[' + CHAR(32) + '-' + CHAR(255) + ']'
PRINT 'Expected'
ELSE
PRINT 'Unexpected'
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx