T-Sql - Order By on Alphanumeric

Question

Hi folks,

i have a list of alphanumeric tokens, say '1a', '1b', '02', '03', '10', '11', etc...

Now, what's the best way to do an order by on this list of tokens?

I am getting '1a', '1b', '10', '11', '02', '03',

but i need it to be

'1a', '1b', '02', '03', '10', '11' 

UPDATE

ok, i am doing this after the suggestion but it's not working.

declare @tokens table(token varchar(20));

insert into @tokens
select '1a'
select '1b'
select '02'
select '10'

select * from @tokens
order by case
 when ISNUMERIC(token) = 1 then right('0000000000'+token+'0',10)
 else right('0000000000'+token,10)
 end

I am getting the response as '1b', '02', '10', '1a'

UPDATE2

It works after making the following change.

declare @tokens table(token varchar(20));

insert into @tokens
select '1a'
insert into @tokens
select '1b'
insert into @tokens
select '02'
insert into @tokens
select '10'


select token from @tokens
order by case
 when ISNUMERIC(token) = 1 then right('0000000000'+token+'0',10)
 else right('0000000000'+token,10)
 end

Thanks to all of you for your nice ideas.

Answer 1

The easiest solution is to pre-pend zeros

Select ...
From Table
Order By Right( '0000000000' + YourColumn, 10)

However, this will not account for alpha characters. In order to deal with alpha characters, then you'd need to know how many potential alpha characters you might have. If there is one, you could do something like:

Select ...
From #Test
Order By Case
    When IsNumeric(NumVal) = 1 Then Right('0000000000' + NumVal + '0', 10)
    Else Right('0000000000' + NumVal, 10)
    End

ADDITION

Test run:

If object_id('tempdb..#Test') is not null
    Drop Table #Test

Create Table #Test ( NumVal varchar(10) )
Insert #Test(NumVal) Values('02')
Insert #Test(NumVal) Values('03')
Insert #Test(NumVal) Values('1a')
Insert #Test(NumVal) Values('1b')
Insert #Test(NumVal) Values('10')
Insert #Test(NumVal) Values('11')

Select NumVal
From #Test
Order By Case
    When IsNumeric(NumVal) = 1 Then Right('0000000000' + NumVal + '0', 10)
    Else Right('0000000000' + NumVal, 10)
    End

Results:
1a
1b
02
03
10
11

A note about my solution. If it is the case that the alphabetic character(s) has special meaning, then as Erick Robertson suggested, you should break up the data into separate columns. The above solution will only handle two very specific cases: an all numeric value, a value with a single trailing alphabetic character. If the data might have multiple alphabetic characters or the alphabetic character is sometimes positioned other than the end of the value, my solution will not work. In addition, it should be noted that my solution will cause a table scan to evaluate the order-able string on each value.

If what you seek is a one-time quick solution, then my approach will work. If you are seeking a long term solution, then either break up the data into separate columns, accept the goofy sort order or add a column that dictates the relative sort order for each value.

Answer 2

The best solution is to have a separate field which stores the int value of the token. You should maintain this column when you maintain the token column. Then when you sort, order by the int value column then the token column. This will allow you to index these columns for fast retrieval of data with large data sets.

Conversion functions from alpha to int are slow and cannot take advantage of indexing to speed up queries. As your data set grows, this type of solution will only get slower and bog down your database.

Answer 3

If you're familiar with C# or VB.net, it might be worth considering writing a CLR function that performs the sorting for you as this sort order is non-standard enough to be quite hard to describe comprehensively and correctly in TSQL.