SQL Sort / Insert the last five characters

Question

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

I have a table with 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, since they are outdated, they need to be considered first. I was advised to fine-tune the last 5 characters of the string, then pass it into a number, and then, possibly, if -0000 turns into negative, then the older ones will sort first. I'm not sure this will work though ...

The problem with sorting it is usually due to the fact that, as you see, in new numbers this is the 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

asp.net, , - , , SQL Server.

, , ?

- datatable...

!!!

Answer 1

- ( ):

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

Answer 2

5 Cast Integer ( !!)

.

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

, , , .

Edit: I was just about to clarify this, but I'm not sure if this is enough for you!