I am using SQL Server, the column is a VARCHAR(50)
and I want to sort it like this:
1A
1B
2
2
3
4A
4B
4C
5A
5B
5C
5N
14 Draft
21
22A
22B
23A
23B
23C
23D
23E
25
26
FR01584
MISC
What I have so far is:
Select *
From viewASD
ORDER BY
Case When IsNumeric(LEFT(asdNumNew,1)) = 1
Then CASE When IsNumeric(asdNumNew) = 1
Then Right(Replicate('0',20) + asdNumNew + '0', 20)
Else Right(Replicate('0',20) + asdNumNew, 20)
END
When IsNumeric(LEFT(asdNumNew,1)) = 0
Then Left(asdNumNew + Replicate('',21), 20)
End
But this SQL statement puts '14 Draft' right after '26'.
Could someone help? Thanks
Your WHERE statement is... oddly complex.
It looks like you want to sort by any leading numeric digits in integer order, and then sort by the remainder. If so, you should do that as separate clauses, rather than trying to do it all in one. The specific issue you're having is that you're only allowing for a single-digit number, instead of two or more. (And there's No such thing as two.)
Here's your fix, along with a SQLFiddle, using two separate calculated columns tests for your ORDER BY. (Note that this assumes the numeric portion of asdNumNew
will fit in a T-SQL int
. If not, you'll need to adjust the CAST and the maximum value on the first ELSE.)
SELECT * FROM viewASD
ORDER BY
CASE
WHEN ISNUMERIC(asdNumNew)=1
THEN CAST(asdNumNew as int)
WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1
THEN CAST(
LEFT(
asdNumNew,
PATINDEX('%[^0-9]%',asdNumNew) - 1
) as int)
ELSE 2147483648
END,
CASE
WHEN ISNUMERIC(asdNumNew)=1
THEN NULL
WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1
THEN SUBSTRING(
asdNumNew,
PATINDEX('%[^0-9]%',asdNumNew) ,
50
)
ELSE asdNumNew
END