I have a column that contains data like this. dashes indicate multi copies of the same invoice and these have to be sorted in ascending order
790711
790109-1
790109-11
790109-2
i have to sort it in increasing order by this number but since this is a varchar field it sorts in alphabetical order like this
790109-1
790109-11
790109-2
790711
in order to fix this i tried replacing the -(dash) with empty and then casting it as a number and then sorting on that
select cast(replace(invoiceid,'-','') as decimal) as invoiceSort...............order by invoiceSort asc
while this is better and sorts like this
invoiceSort
790711 (790711) <-----this is wrong now as it should come later than 790109
790109-1 (7901091)
790109-2 (7901092)
790109-11 (79010911)
Someone suggested to me to split invoice id on the - (dash ) and order by on the 2 split parts
like=====> order by split1 asc,split2 asc (790109,1)
which would work i think but how would i split the column.
The various split functions on the internet are those that return a table while in this case i would be requiring a scalar function.
Are there any other approaches that can be used? The data is shown in grid view and grid view doesn't support sorting on 2 columns by default ( i can implement it though :) ) so if any simpler approaches are there i would be very nice.
EDIT : thanks for all the answers. While every answer is correct i have chosen the answer which allowed me to incorporate these columns in the GridView Sorting with minimum re factoring of the sql queries.
See Question&Answers more detail:os