1

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
Vijay Hulmani
  • 969
  • 8
  • 17
  • does the Isnumeric(id) = 1 get hit at all? the field ia varchar? – lloydom Aug 02 '13 at 10:33
  • It will get hit for 10 and 100!! – Vijay Hulmani Aug 02 '13 at 10:34
  • Do you have at maximum three digits? So 1, 10, 100, 700 BUT NOT 1000, 2000, 10000? – xanatos Aug 02 '13 at 10:35
  • It will be always be 3 digits and most of the time 700 only. – Vijay Hulmani Aug 02 '13 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 –  Aug 02 '13 at 10:37
  • `LEFT(Replicate('0',101) + ID, 100) ` this is wrong... You create a string of 101x0 + ID and take the first 100 characters... You'll only take 100x0! You probably meant `RIGHT`. – xanatos Aug 02 '13 at 10:37
  • Vijay i found a similar issue, look at this link for ideas am sure that will work http://stackoverflow.com/questions/16862147/sorting-varchar-column-with-alphanumeric-entries?rq=1 – lloydom Aug 02 '13 at 10:38
  • I have achived by spliting strings and sorting.But i wanted to know is there anything which is more generilistic ?? – Vijay Hulmani Aug 02 '13 at 10:39
  • After Alphabet B or Anyone eLSE it always 3 characheter or more is – Amit Singh Aug 02 '13 at 10:41
  • @vijay Sorry, but I have to vote for close as unclear until you describe the exact sorting algorithm you want. There are multiple algorithms that will give the result you give as desired. – Joachim Isaksson Aug 02 '13 at 11:01
  • @JoachimIsaksson The table MyTable is actually a temp table in a stored procedure.I have performing some query and inserting the resultset into it.And then performing a sorting on it. – Vijay Hulmani Aug 02 '13 at 11:27

2 Answers2

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)
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
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.

Hart CO
  • 34,064
  • 6
  • 48
  • 63