Alphanumeric Sorting using SQL Server / C#

Question

I have alphanumeric numbers. After applying sorting thru SQL Server ORDER BY clause, I get following result

select * 
from WO
where WOCode = AnyNumber
order by [ColumnName]

Result:

Required result

By `Sorting thru SQL server` do you mean an `ORDER BY` clause? Are you looking for a C# answer or a SQL answer? What have you tried? Where do we start? — Ňɏssa Pøngjǣrdenlarp, Dec 27 '17 at 19:44
There is still very little to go on - what is being sorted? You will want to research *Natural Sort* - many good answers here — Ňɏssa Pøngjǣrdenlarp, Dec 27 '17 at 19:48
I updated my Question ,, I was trying to BOLD 39660A10 and 39660A11 — SanamShaikh, Dec 27 '17 at 19:53
unfortunately you lose the line breaks unless you format as code and then the markup for bold doesn't work. — Martin Smith, Dec 27 '17 at 19:55

Tommaso Belluzzo · Answer 1 · 2017-12-27T20:20:30.157

2

Here is a quick and dirty solution:

SELECT *
FROM table 
ORDER BY LEN(Field) ASC, Field ASC

Demo here.

edited Dec 27 '17 at 20:20

answered Dec 27 '17 at 19:52

Tommaso Belluzzo

23,232
8
74
98

score 1 · Answer 2 · edited Dec 27 '17 at 21:52

Assuming that the letter A is always in the same position, and the characters after it are integers only.

Then you can do this:

WITH CTE AS
(
    SELECT 
        WOCode,
        CAST(SUBSTRING(WOCode, CHARINDEX('A', WOCode) + 1, 
                       LEN(WOCode) - CHARINDEX('A', WOCode) + 1) AS INT) AS DisplayOrder
    FROM 
        WO
) 
SELECT * 
FROM CTE
ORDER BY DisplayOrder;

Demo

Results:

|   WOCode |
|----------|
|  39660A1 |
|  39660A2 |
|  39660A3 |
|  39660A4 |
|  39660A5 |
|  39660A6 |
|  39660A7 |
|  39660A8 |
|  39660A9 |
| 39660A10 |
| 39660A11 |

You can also use TRY_CAST to avoid errors that might result because of using cast with non integer values (Thanks to @zambonee for suggestion):

WITH CTE AS
(
    SELECT 
        WOCode,
        CASE 
           WHEN TRY_CAST(WOCode AS INT) IS NULL 
              THEN CAST(SUBSTRING(WOCode, 
                          CHARINDEX('A', WOCode) + 1, 
                          LEN(WOCode) - CHARINDEX('A', WOCode) + 1) AS INT) 
              ELSE 0 
        END AS DisplayOrder
    FROM  
        WO
) 
SELECT * 
FROM CTE
ORDER BY DisplayOrder;

updated demo

Alphanumeric Sorting using SQL Server / C#

2 Answers2