Sorting in TSQL

Question

I am using SQL Server 2008.

I would like to sort my result by using the DetailRefNumber. However, I could not achieve to sort like

2.1 
2.2 
2.3 
xxx
xxx
2.10
2.11
2.12

Could you please guide me how I could do it?

Thanks.

Answer 1

If that DetailRefNumber will always be a number, separated by a dot, you could add two computed columns to that table which basically take that column apart into two separate, numeric values:

alter table dbo.YourTable
  add RefNrMajor AS CAST(LEFT(DetailRefNumber, CHARINDEX('.', DetailRefNumber) - 1) AS INT) PERSISTED

alter table dbo.YourTable
  add RefNrMinor AS CAST(SUBSTRING(DetailRefNumber, CHARINDEX('.', DetailRefNumber) + 1, 999) AS INT) PERSISTED

and now you can order by those new numeric columns:

SELECT (columns)
FROM dbo.YourTable
ORDER BY RefNrMajor, RefNrMinor