Order data on scheduler by data from query

Question

Col1 Col2 Col3

98 BT XYZ Michael | 2013-12-24 10:00:00.0000000 | 2013-12-24 11:00:00.0000000

99 BT PPRL James1212 | 2013-12-24 09:46:04.0000000 | 2013-12-24 10:46:04.0000000

100 LV RD JOHN | 2013-12-24 12:52:22.0000000 | 2013-12-24 13:52:22.0000000

101 LV RD JOHN | 2013-12-24 12:58:05.0000000 | 2013-12-24 13:58:05.0000000

102 LV RD JOHN | 2013-12-24 13:13:56.0000000 | 2013-12-24 14:13:56.0000000

So, my query returns 6 column. I am showing 3 of them here. Col1 is of datatype varchar(max) and the col2,col3 are of type datetime2. Now i show this data on a radscheduler. The appointments on it show as 101,100,102,99,98. I want to show them alphabetically. I mean 98,99,100,101,102. Most of the times the data starts like this A number and some data like this (43 blah blah).SOmetimes it starts as "** blah blah". How can I do that? Any ideas? If you need more info,please ask. Thanks.

Answer 1

ok explain why it won't work.

Declare @i varchar(100)='98 BT XYZ Michael'

select substring(@i,PATINDEX('%[0-9]%', @i),PATINDEX('%[A-Z]%', @i)-1)