Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database with a VARCHAR column that contains integers of varying length. I want to sort them so 10 comes after 9, not 1, and 70A comes after 70. I was able do this with PATINDEX(), a CTE, and CASE statements in the WHERE clause.

However, I was wondering if there was a collation where this would be unecessary.

share|improve this question

3 Answers 3

up vote 5 down vote accepted

No. Collation is about alphabetical sorting, depending on code page, accent, case, width, kana. Numbers characters (0-9) have none of there properties.

So 9 is always after 10B in any sort.

You have to split it up as you noted or sort like this:

ORDER BY
    RIGHT('                              ' + MyColumn, 30)

The length in the right determines how many spaces you have.

You could of course:

  • have 2 columns to make this unnecessary (and far quicker) and have a computed column to combine them
  • insist on leading zeros
  • right justify in a char (a stored version of my RIGHT above)

The latter 2 suggestions are like my RIGHT above and slightly different. Quicker to sort (no processing of the colukmn needed) but more storage required

share|improve this answer
    
i don't see how this works. It breaks for 2, 2a, 3, etc... –  Mladen Prajdic Jul 13 '11 at 17:35
    
@Mladen Prajdic : you're correct, oops. Forgot about the trailing alphabeticals –  gbn Jul 13 '11 at 17:37

I would setup a computed column then sort based on that. Something like

CAST( 
     CASE WHEN IS_NUMERIC(left(OtherColumn, 2) = 1) then 
         left(OtherColumn,2) 
     else 
         left(otherColumn, 1)  
AS INT)

Then use this column to sort by as you can now index the column.

share|improve this answer
    
That's really useful to know about for similar problems. However, in this case I can't change the schema. –  Justin Dearing Jul 13 '11 at 18:35
    
Can you add to the schema? Barring a computed column, you could always create a view - albeit this won't really be optimizable like a computed column could be. –  Aaron Bertrand Jul 13 '11 at 19:03
    
If you do an indexed view, and have Enterprise edition your query will use the indexed view automatically if it can figure out what you are trying to do. If standard edition you'd need to use the WITH (NOEXPAND) to trigger the indexed view to be used. At that point you'd need to have the case statement in your order by but it should work, I think. –  mrdenny Jul 13 '11 at 19:06
    
You don't need to create a computed column. You can use that expression directly in the ORDER BY clause –  a_horse_with_no_name Mar 1 '12 at 9:07
    
If you want to guarantee an index or table scan sure you can. If you want to be able to index the value then a computed column or indexed view is needed. –  mrdenny Mar 5 '12 at 18:25

If you want a painful way to prove what @gbn is saying (essentially that you can't tell a collation to order substrings differently), you can make a quick #temp table that has a coefficient for the order you expect, and see if ordering by any collation returns the same order:

CREATE TABLE #foo(id INT, n NVARCHAR(10));

CREATE TABLE #bar(collation SYSNAME);

SET NOCOUNT ON;

INSERT #foo SELECT 1,'1'
UNION SELECT 2,'2'
UNION SELECT 3,'3'
UNION SELECT 4,'6'
UNION SELECT 5,'10'
UNION SELECT 6,'10A'
UNION SELECT 7,'10B'
UNION SELECT 8,'11';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
    WITH x AS 
    (
        SELECT n, rn = ROW_NUMBER() OVER 
        (ORDER BY n COLLATE ' + name + ') FROM #foo
    ) 
    INSERT #bar 
    SELECT TOP (1) ''' + name + ''' FROM x
    WHERE NOT EXISTS
    (
        SELECT COUNT(*) FROM #foo AS f
        WHERE f.id = x.rn
        AND f.n <> x.n
    );' FROM sys.fn_helpcollations();

EXEC sp_executesql @sql;

SELECT collation FROM #bar;

GO
DROP TABLE #foo, #bar;

This runs for me in about 10 seconds and yields 0 rows - meaning no collation available to SQL Server (at least 2008 R2, haven't tried Denali) will sort in the way you expect. You need a different way to define sorting.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.