character order of precedence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

character order of precedence

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-08 : 06:55:31

Can anyone explain how order of precence works when comparing numbers inside speechmarks with characters? It does not seem to be ASCII comparison.

declare @Qu as varchar(10) ='?'
declare @Hy as varchar(10) ='-'
declare @Num as varchar(10) ='4'

select case when @Hy < @Num then 'hyphen less' else 'number more' end as Test
,case when @Qu < @Num then 'Question less' else 'number more' end as Test1
SELECT ASCII('?')as Qu, ASCII('4') as [4],ASCII('-') as [-]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-08 : 08:26:51

It isn't sorting by it's ASCII number, if that's what you mean. Sorting characters actually depends on the collation you're using. You'd have to look up the rules for your specific collation.
Jim

just as an example
select min(number)
from
(select [number] = '9999' union select '35890'
)a

Everyday I learn something that somebody else already knew

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-08 : 08:33:19

I would also by interested in the order of precedence on character matching with various collations as I have never found a reference which made sense.
The follwoing is an old reference which sort of explained a problem I was having with hypens:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305704

Any problems can usually be worked around by casting to a binary collation but this has always seemed unsatisfactory.
eg


SELECT
	CASE
		WHEN @Hy COLLATE latin1_general_bin < @Num COLLATE latin1_general_bin
		THEN 'hyphen less'
		ELSE 'number less'
	END AS Test
	,CASE
		WHEN @Qu COLLATE latin1_general_bin < @Num COLLATE latin1_general_bin
		THEN 'Question less'
		ELSE 'number less'
	END AS Test1

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-08 : 11:10:52

The following will give the character order for your default instance collation.
In addition non-binary collations seem to use word sorting which ignores certain characters.
As mentioned before, I would be interested if anyone could point me at the exact sorting rules as I have never seen them defined.
I am particularly interested in:

Latin1_General_CI_AS
SQL_Latin1_General_CP1_CI_AS
Latin1_General_100_CI_AS


CREATE TABLE #t
(
	c char(1) NOT NULL
	,a tinyint NOT NULL
)
INSERT INTO #t
SELECT CHAR(number), number
FROM master.dbo.spt_values
WHERE [type] = 'P'
	AND number BETWEEN 32 AND 127

SELECT c, a
	,DENSE_RANK() OVER (ORDER BY c) AS r
FROM #t
ORDER BY c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources