Custom Sorting Function - of SQL Server

Question

Hi Guys,

I have a column in SQL Server 2005 that stores a version number as a string that i would like to sort by. I have been unable to find out how to sort this column, although i am guessing it would be some kind of custom function or compare algorithm.

Can anyone point me in the right direction of where to start? I may be googling the wrong stuff.

Cheers

Tris

Answer 1

I would look at using a persisted computed column which processes the string into an int or string or something appropriate for sorting in the native SQL Server sort - i.e.

'1.1.1.1' -> '001.001.001.001'
'10.10.10.10' -> '010.010.010.010'
'1.10.1.10' -> '001.010.001.010'

So that you can sort by the computed column and get expected results.

Alternatively, you can use such an operation inline, but it might be very slow. In addition scalar UDFs are extremely slow.

Answer 2

I'd use separate int columns (e.g. MajorCol + MinorCol if you are tracking major + minor versions) and run something like

order by MajorCol, MinorCol

in my query.