sorting an alphanumeric column in sql

Question

I have a table 'MyTable' with the following the column, ID varchar(50).

ID
----------
10
100
700-6 0110B512
700-6 0110B513
700-8 0110B512
700-9 0110B512

I need to sort this column with the output

ID
----------
10
100
700-6 0110B512
700-8 0110B512
700-9 0110B512
700-6 0110B513

Please help!!

I have tried like this but the output is not as desired.

SELECT * FROM MyTable
ORDER BY
Case    
When IsNumeric(ID) = 1 then LEFT(Replicate('0',101) + ID, 100) 
When IsNumeric(ID) = 0 then RIGHT(Replicate('0',101) + ID, 100) 
Else ID  
END
ASC

does the Isnumeric(id) = 1 get hit at all? the field ia varchar? — lloydom
– lloydom, Commented Aug 2, 2013 at 10:33
Do you have at maximum three digits? So 1, 10, 100, 700 BUT NOT 1000, 2000, 10000? — xanatos
– xanatos, Commented Aug 2, 2013 at 10:35
It will be always be 3 digits and most of the time 700 only. — Vijay Hulmani
– Vijay Hulmani, Commented Aug 2, 2013 at 10:36
@vijay in mysql if you just write select * from table_name orderby column_name then its automatically sorts,i do not know whether it works in sql server 2005 or not.Just check it — user2028750
– user2028750, Commented Aug 2, 2013 at 10:37

t-clausen.dk · Accepted Answer · 2013-08-02 10:46:19Z

1

DECLARE @t table(id varchar(50))
INSERT @t values ('10')
INSERT @t values('100')
INSERT @t values('700-6 0110B512')
INSERT @t values('700-6 0110B513')
INSERT @t values('700-8 0110B512')
INSERT @t values('700-9 0110B512')

SELECT * 
FROM @t 
ORDER BY cast(left(id, 3) as int), stuff(id, 1, 6, ''), substring(id, 5,1)

edited Aug 2, 2013 at 10:46

answered Aug 2, 2013 at 10:41

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

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

Comments

Hart CO · Accepted Answer · 2013-08-02 13:03:49Z

1

Assuming fixed leading/trailing integers, I think this is the simplest it could be:

SELECT *
FROM table1 
ORDER BY LEFT(id,3) ,RIGHT(id,3) ,ID

SQL Fiddle

Could cast either portion as INT if needed, not clear from sample.

edited Aug 2, 2013 at 13:03

answered Aug 2, 2013 at 11:24

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

1 Comment

Joachim Isaksson Over a year ago

Puts 20 after 100, but there's nothing in the question saying that's wrong, so... :)

Collectives™ on Stack Overflow

sorting an alphanumeric column in sql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related