SQL Sort/Cast last five chars question

Question

I have a sticky SQL issue and I'm not the best with SQL...

I have a table that has a varchar(40) column that has data in the following two formats:

nn-nnn1nnn00000
nn-nnn-nnn-0000

The second data type is outdated; however because they are outdated they need to be viewed first in order. It was recommended to me to substring the last 5 chars of the row, then cast it to a number and then perhaps, if the -0000 gets turned into a negative then the older ones will sort first. I'm not sure if this would work though...

The problem with sorting it normally is because as you can see in the new ones is a number in the 7th position. So when I try to sort I get:

nn-nnn-nnn-0001
nn-nnn-nnn-0002
nn-nnn-nnn-0003
nn-nnn1nnn00002 <-Should be 5th
nn-nnn2nnn00003 <-Should be 6th
nn-nnn3nnn00001 <-Should be 4th

Because this is for a asp .net page I was thinking about doing some crazy stuff with datatables but I know this would be better suited to be handled by SQL Server.

Is there a way to sort by the last five chars in a way where the older ones will show first while still allowing the new ones to sort next correctly?

I really don't want to do some weird stuff in a datatable...

Thanks a million!!!

Answer 1

I would try with something like this (though it may be considered clunky):

Select
  Col, Case Left(Right(Col, 5), 1) When '-' Then 0 Else 1 End As Old
From
  Table
Order By
  Old, Col

Answer 2

The Right 5 characters & Cast as Integer works perfectly (after modification!!)

Check this out..

With MyTable AS
(
SELECT 'nn-nnn-nnn-0001' as MyData
UNION SELECT 'nn-nnn-nnn-0002'
UNION SELECT 'nn-nnn-nnn-0003'
UNION SELECT 'nn-nnn1nnn00002'
UNION SELECT 'nn-nnn2nnn00003'
UNION SELECT 'nn-nnn3nnn00001'
)
SELECT *
FROM MYTable
ORDER BY CASE WHEN SUBSTRING (MyData, 11, 1) = '-' THEN 0 ELSE 1 END, 
    CAST (REPLACE (RIGHT (MyData, 5), '-', 0) AS INTEGER)

MyData
---------------
nn-nnn-nnn-0001
nn-nnn-nnn-0002
nn-nnn-nnn-0003
nn-nnn3nnn00001
nn-nnn1nnn00002
nn-nnn2nnn00003

(6 row(s) affected)

Answer 3

You can create computed column, use deterministic user defined function to able to create index for that column and use that column to sort.

Edit: I was just going to clarify that but i am not sure if it is weird enough for you!