469,331 Members | 1,769 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

Unexpected LIKE behavior

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.

--
(remove a 9 to reply by email)
Apr 22 '07 #1
2 1884
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_General_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
Apr 22 '07 #2
Dimitri Furman (df*****@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, es****@sommarskog.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
Apr 22 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by He Shiming | last post: by
7 posts views Thread by Dave Hansen | last post: by
4 posts views Thread by Generic Usenet Account | last post: by
9 posts views Thread by Jeff Louie | last post: by
62 posts views Thread by ashu | last post: by
1 post views Thread by Kevin Walzer | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.