Alphanumeric and Numeric sorting in SQL Server

Question

I need to apply sorting on Alphanumeric and Numeric sorting.

Example:

319,
519, 
620, 
1154, 
1195, 
319lov,
319, 
620lov

I need result like:

319,
319,
319lov,
519, 
620, 
620lov,
1154, 
1195,

I have applied following code to sort:

SELECT Name 
FROM TableName
ORDER BY CAST(CASE
                 WHEN Name LIKE '[0-9][0-9][0-9][0-9]%' THEN LEFT(Name ,4)
                 WHEN Name LIKE '[0-9][0-9][0-9]%' THEN LEFT(Name ,3)
                 ELSE NULL 
              END AS INT)

Got result like:

319,
319lov,
319,
519, 
620,
620lov, 
1154, 
1195,

http://stackoverflow.com/questions/16862147/sorting-varchar-column-with-alphanumeric-entries — CoSpringsGuy
– CoSpringsGuy, Commented Jan 25, 2016 at 20:42

Mihai · Accepted Answer · 2016-01-25 20:51:23Z

1

SELECT name FROM t 
ORDER BY CONVERT(INT, LEFT(name, PATINDEX('%[^0-9]%', name+'z')-1)),name;

You order by all the numbers up to the first non number,the letter is in there to treat all strings the same.

FIDDLE

edited Jan 25, 2016 at 20:51

answered Jan 25, 2016 at 20:44

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lukasz Szozda Over a year ago

Nope,counterexample.

Collectives™ on Stack Overflow

Alphanumeric and Numeric sorting in SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related