CODEWITHSUNDEEP
sql-server
Nitendra Jain
Nitendra Jain

Reputation: 499

How to sorting nvarchar column after 4 character?

I have a nvarchar column and when I am selecting using below query this it is giving below output - 

SELECT DISTINCT [VERSION]   
FROM IMPORT.BUDGETIMPORT 
WHERE VERSION LIKE 'Test'+'18'+'%' 
ORDER BY [VERSION] ASC

Output:

Test18
Test18.1
Test18.10
Test18.2
Test18.3
Test18.4
Test18.5
Test18.6

I want to order it by number while selecting - 

Test18
Test18.1
Test18.2
Test18.3
Test18.4
Test18.5
Test18.6
Test18.10

How could I sorted based on number only.

Upvotes: 2

Views: 62

Answers (3)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

Another option to perform it is casting the version as float;

SELECT DISTINCT [VERSION], cast(SUBSTRING([VERSION], 5, 10) as float) as VersionAsFloat
FROM IMPORT.BUDGETIMPORT 
WHERE VERSION LIKE 'Test'+'18'+'%' 
ORDER BY VersionAsFloat ASC

Upvotes: 0

shyamu
shyamu

Reputation: 86

Try this one.

Order first by length of the string and then by the string itself.

Query

SELECT [VERSION]
FROM IMPORT.BUDGETIMPORT 
WHERE [VERSION] LIKE 'Test18'+'%' 
GROUP BY [VERSION]  
ORDER BY LEN([VERSION]), [VERSION];

demo

Upvotes: 3

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

For numeric Datatypes, 18.1 is same as 18.10 and for string datatypes, 18.10 will come before 18.2

Try the below method to get your expected result

declare @t table
(
    [version] nvarchar(50)
)

insert into @t
values('Test18'),
('Test18.1'),
('Test18.10'),
('Test18.2'),
('Test18.3'),
('Test18.4'),
('Test18.5'),
('Test18.2'),
('Test18.3'),
('Test18.4'),
('Test18.5'),
('Test18.6')

;with cte
as
(
    select 
        rn = ROW_NUMBER() OVER(PARTITION BY [VERSION] ORDER BY [VERSION]),
        ln = LEN([VERSION]),
        [version] 
        from @t 
)
select
    *
    from cte
        WHERE rn = 1
        order by ln

Upvotes: 0

Related Questions