November 20, 2017 at 5:10 am
Can anyone give a convincing argument as to why SQL 2016 gives different results for these two queries?
The top non-unicode query returns NO, where as the lower UniCode query return YES.
Non-UnicodeIF 'GP_81003' BETWEEN 'GP_810-00000' AND 'GP_810-99999'
PRINT 'YES'
ELSE
PRINT 'NO'
UnicodeIF N'GP_81003' BETWEEN N'GP_810-00000' AND N'GP_810-99999'
PRINT 'YES'
ELSE
PRINT 'NO'
November 20, 2017 at 5:17 am
What collation are you using? They both come out as YES for me (SQL Server 2016 SP1, collation Latin1_General_CI_AI)
John
November 20, 2017 at 5:22 am
it must be down to you using SQL collation.
Here you might find the answer to your question
... For example, if you are using the SQL collation "SQL_Latin1_General_CP1_CI_AS", the non-Unicode string 'a-c' is less than the string 'ab' because the hyphen ("-") is sorted as a separate character that comes before "b". However, if you convert these strings to Unicode and you perform the same comparison, the Unicode string N'a-c' is considered to be greater than N'ab' because the Unicode sorting rules use a "word sort" that ignores the hyphen...
November 20, 2017 at 6:08 am
What does this query return in your database?
😎
SELECT
SDB.name
,SDB.collation_name
FROM sys.databases SDB
WHERE SDB.database_id = DB_ID()
November 20, 2017 at 6:27 am
Thank you for your replies.
FYI:
My Collation was slightly different to the first responder:
SQL_Latin1_General_CP1_CI_AS : So just Accent Senstive different.
The linked page that Emil B references explains why this is happening...
Unicode sorting rules use a "word sort" that ignores the hyphen
and...
With SQL collation "SQL_Latin1_General_CP1_CI_AS", the hyphen ("-") is sorted as a separate character
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply