CODEWITHSUNDEEP
sqlsql-serverstringsorting
Varsa Lai
Varsa Lai

Reputation: 23

Sort SQL Server column that contains number in the varchar

I've an SQL Server 2016 table that stores following data in a varchar like bellow:

1
1-1
1-1-1
1-2
1-1-1-20
1-1-1-5
1-1-2
1-1-1-25
1-15-2
1-1.5

It will sort asc as default like:

1
1-1
1-1.5
1-1-1
1-1-1-20
1-1-1-25
1-1-1-5
1-1-2
1-15-2
1-2

But I want to sort each number between '-' was sort as number like this

1
1-1
1-1.5
1-1-1
1-1-1-5
1-1-1-20
1-1-1-25
1-1-2
1-2
1-15-2

Upvotes: 2

Views: 106

Answers (3)

Gagan Burde
Gagan Burde

Reputation: 328

Please find below answer

    **Create table test
(
 id varchar(250) 

)**

 - insert into test  values('1')

   insert into test  values('1-1')

   insert into test  values('1-1-25')

   insert into test  values('1-1-5')

   insert into test  values('1-1-10')

        SELECT id FROM test

    ORDER BY 

    CASE WHEN PatIndex('%[-]%',id) > 0

          THEN LEFT(id,PatIndex('%[-]%',id)-1)

          ELSE id END * 1

    ,CASE WHEN PatIndex('%[-]%',REVERSE(id)) > 0

          THEN RIGHT(id,PatIndex('%[-]%',REVERSE(id))-1)

          ELSE NULL END * 1

Upvotes: 0

Fmanin
Fmanin

Reputation: 575

Based on you example, Case statement into the Order By would help you:

SELECT column1 FROM@TableName
ORDER BY  CASE Column1
when '1' then 1
when '1-1' then 2
when '1-1.5' then 3
when '1-1-1' then 4
when '1-1-1-5' then 5
when '1-1-1-20' then 6
when '1-1-1-25' then 7
when '1-1-2' then 8
when '1-2' then 9
when '1-15-2' then 10
else column1 END

Upvotes: 1

salaudeen
salaudeen

Reputation: 88

Hopefully this will helps

declare  @TableName table(Column1 varchar(10))

insert into @TableName values
('1'),
('1-1'),
('1-1-1'),
('1-2'),
('1-1-1-20'),
('1-1-1-5'),
('1-1-2'),
('1-1-1-25'),
('1-15-2'),
('1-1.5')

DECLARE @delimiter VARCHAR(50) = '-' 
;WITH CTE AS
( 
    SELECT column1,
        CAST('<M>' + REPLACE(column1, @delimiter , '</M><M>') + '</M>' AS XML)  
        AS [Description XML]
    FROM  @TableName
)
SELECT column1

FROM CTE

Order by [Description XML].value('/M[1]', 'float'), [Description XML].value('/M[2]', 'float'), [Description XML].value('/M[3]', 'float'), [Description XML].value('/M[4]', 'float'),
[Description XML].value('/M[5]', 'float'), [Description XML].value('/M[6]', 'float'), [Description XML].value('/M[7]', 'float'), [Description XML].value('/M[8]', 'float'), [Description XML].value('/M[9]', 'float')

Upvotes: 0

Related Questions