Posted by Erland Sommarskog on 04/22/07 09:37

Dimitri Furman (dfurman@cloud99.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_General_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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]