Sort Varchar

combine string and number like 1.1


declare @t table(id int ,data varchar(50))
declare @t3 table(id int, data3 varchar(50))
insert into @t
select '1','Rak 10' union all
select '2','Rak 1.2' union all
select '3','Rak 122' union all
select '4','Rak 2' union all
select '5','Area 2'union all
select '6','Area Lantai 1' union all
select '7','Rak 1'
 
declare @xml xml,
        @max_len int
     

insert @t3 (id, data3)
select id, SubString(data,(LEN (data) - CharIndex (' ', REVERSE(data))+2),LEN (data)) as data2
from @t

set @xml =
(
select data3,
 cast('<i>' + replace(data3,'.','</i><i>') + '</i>' as xml)
from @t3
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(50)')))
from @xml.nodes('/id_root/i') x(i)

select T.data, T3.data3, srt.srtvalue
from @t3 T3
join @t T
ON T3.id = T.id
cross apply(
    select
case
when ISNUMERIC(x.i.value('.','varchar(50)')) = 1
then right(replicate('0',@max_len) + x.i.value('.','varchar(50)'),@max_len)
else x.i.value('.','varchar(50)')
end + '.'
    from @xml.nodes('/id_root/i') x(i)
    where x.i.value('../data3[1]','varchar(50)') = [T3].data3
    for xml path('')
) as srt(srtvalue)
order by SubString (data,1 ,CharIndex (' ', data)) ,srt.srtvalue

Result:
data
--------------------------------------------------
Area Lantai 1
Area 2
Rak 1
Rak 1.2
Rak 2
Rak 10
Rak 122


(7 row(s) affected)