January 27, 2009 at 9:15 am
DECLARE @a VARCHAR(30)
SET @a = '\'
SELECT 1
WHERE @a LIKE '%[.-9]%'
PRINT ASCII('.')
PRINT ASCII('/')
PRINT ASCII('0')
PRINT ASCII('1')
PRINT ASCII('2')
PRINT ASCII('3')
PRINT ASCII('9')
PRINT ASCII('\')
How can '\' be picked up by this LIKE? A range of .-9 should pick up ./0123456789 but for some reason it is picking up the backslash as well. Yes, I could just change the LIKE to '%[./0123456789]%'(which '\' fails) but my curiosity has been let loose. A range of [0-9] does not net a positive result for a backslash however a range of [/-0](should only match / and 0) does! Is Sql Server doing something funky with the backslash during a range check?
January 27, 2009 at 10:36 pm
LIKE comparisons are based on collation, not ascii value. As noted in this thread, SQL_Latin1_General_CP1_CI_AS is Dictionary sort, which places both '\' and '/' between '.' and '9', along with about 74 other characters that may or may not have ascii values between 46 and 57.
You can verify this sorting by using ORDER BY on your sample values (plus a few for effect):
select * from (
select '.' as alice
union select '/'
union select ':'
union select ';'
union select ']'
union select '['
union select '2'
union select '€'
union select '4'
union select '5'
union select '6'
union select '7'
union select '8'
union select '9'
union select '\'
) as bob
order by alice
You should see a sort order similar to this
January 29, 2009 at 9:02 am
I always assumed collations appended the special characters to a basic character set like ascii instead of interweaving with(or totally rearranging) it. Thanks for the education!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply