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)

Sort Varchar (eq 1.1)

declare @temp table (id varchar(255))

insert into @temp (id) values
  ('1.1.a.1'),('1.1.aa.2'),
  ('1.1.b.3'),('1.1.a.4'),
  ('1.1.a.5'),('1.1.a.6'),
  ('1.1.a.7'),('1.1.a.8'),
  ('1.1.a.9'),('21.1.a.10'),
  ('1.1.a.11'),('1.1.b.1'),
  ('2.1.b.2'),('1.2.a.1'),
  ('1.10.a.1'),('1.11.a.1'),
  ('1.20.a.1'),('101.20.a.2'),
  ('1.20.a.150'),('1.1'),
  ('1.2'),('1')


declare @xml xml,
        @max_len int

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

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

select @max_len


select [id], srt.srtvalue
from @temp
cross apply(
    select case when ISNUMERIC(x.i.value('.','varchar(10)')) = 1 then right(replicate('0',@max_len) + x.i.value('.','varchar(10)'),@max_len) else x.i.value('.','varchar(10)') end + '.'
    from @xml.nodes('/id_root/i') x(i)
    where x.i.value('../id[1]','varchar(50)') = [@temp].id
    for xml path('')
) as srt(srtvalue)
order by srt.srtvalue

Result:
id
-------------------------
1
1.1
1.1.a.1
1.1.a.4
1.1.a.5
1.1.a.6
1.1.a.7
1.1.a.8
1.1.a.9
1.1.a.11
1.1.aa.2
1.1.b.1
1.1.b.3
1.2
1.2.a.1
1.10.a.1
1.11.a.1
1.20.a.1
1.20.a.150
2.1.b.2
21.1.a.10
101.20.a.2


(22 row(s) affected)

Sort VARCHAR on VARCHAR coloum (varchar + int)

SET NOCOUNT ON

Declare @Table table
(
    Id  INT Identity (1, 1),
    StringValue VarChar (30)
)

INSERT INTO @Table (StringValue) VALUES ('CAR 10')
INSERT INTO @Table (StringValue) VALUES ('CAR 20')
INSERT INTO @Table (StringValue) VALUES ('CAR 2')
INSERT INTO @Table (StringValue) VALUES ('CAR 3')
INSERT INTO @Table (StringValue) VALUES ('CAR 4')

INSERT INTO @Table (StringValue) VALUES ('SHIP 32')
INSERT INTO @Table (StringValue) VALUES ('SHIP 310')
INSERT INTO @Table (StringValue) VALUES ('SHIP 320')
INSERT INTO @Table (StringValue) VALUES ('SHIP 33')
INSERT INTO @Table (StringValue) VALUES ('SHIP 34')


SELECT Id,
    SubString (StringValue, 1, CharIndex (' ', StringValue)) ObjectName,
    CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue))) ObjectId
FROM @Table
ORDER BY 2, 3

SELECT Id, StringValue
FROM @Table
ORDER BY
    SubString (StringValue, 1, CharIndex (' ', StringValue)),
    CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue)))