Luis Cazares (8/28/2015)

---

Of course, you need some tweaking to make it numeric.

CREATE TABLE #SampleData( num varchar(100));

INSERT INTO #SampleData VALUES

('1000:001'),

('1001:001'),

('1002:001'),

('999:001'),

('998:001'),

('99:001'),

('1:001'),

('2:001'),

('3:001');

--This won't work correctly

SELECT * FROM #SampleData ORDER BY num

--This should work for you

SELECT * FROM #SampleData

ORDER BY CAST( LEFT( num, CHARINDEX( ':', num + ':') - 1) as int),

SUBSTRING( num, CHARINDEX( ':', num + ':') + 1, 8000)

--Or maybe this

SELECT * FROM #SampleData

ORDER BY CAST( REPLACE( num, ':', '.') as decimal(12, 3))

GO

DROP TABLE #SampleData