CODEWITHSUNDEEP
sql-server
eshaa
eshaa

Reputation: 406

Sort alphanumeric SQL Server column

How to sort the below following values from column Operation_type?

1.Administration
13.Legal And Contractual Matters
14.Financial
15.Personnel
16.Procurement Of Materials, Works And Services
21.Business Management
3.Information System

I tried the below approach but it fails to execute.

select distinct(Operation_type) 
from tableA  
order by cast(case when Operation_type like '%[0-9]' then right(Operation_type , 1) else null end as int)

Below is the expected output:

1.Administration
3.Information System 
13.Legal And Contractual Matters
14.Financial
15.Personnel
16.Procurement Of Materials, Works And Services
21.Business Management

PS: I need to get the distinct column values, not using select *

Upvotes: 0

Views: 35

Answers (3)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

I would change your ORDER BY to something like below:

select Operation_type
from (
    select distinct(Operation_type) Operation_type
    from #tableA
    ) tmp
order by CAST(LEFT(operation_type, charindex('.', operation_type) - 1) as int)

Full working script:

create table #tableA (operation_type nvarchar(max))

insert into #tableA (operation_type) values (
'1.Administration'), (
'13.Legal And Contractual Matters'), (
'14.Financial'), (
'15.Personnel'), (
'16.Procurement Of Materials, Works And Services'), (

select Operation_type
from (
    select distinct(Operation_type) Operation_type
    from #tableA
    ) tmp
order by CAST(LEFT(operation_type, charindex('.', operation_type) - 1) as int)

And the output of the above query:

enter image description here

Upvotes: 2

Martin
Martin

Reputation: 16453

You could use CHARINDEX to find the position of the dot (.) in the column and use the numbers before this to ORDER your output:

SELECT *
  FROM tableA  
  ORDER BY CAST(LEFT(Operation_type, CHARINDEX('.', Operation_type) - 1) AS INT)

This is entirely dependent on their always being a dot (.) in the column though.

A working fiddle showing this is here.

If you can't guarantee that the dot is always present, this version uses a sub-query to deal with entries that do not contain it:

SELECT Operation_type
  FROM (
        SELECT *,
               CASE
                 WHEN CHARINDEX('.', Operation_type) > 0 THEN CAST(LEFT(Operation_type, CHARINDEX('.', Operation_type) - 1) AS INT)
                 ELSE 0
               END AS OrderCol
          FROM tableA  
       ) a
  ORDER BY OrderCol

In this case, the entries without a dot will appear first in the list. You could move them to the end by changing ELSE 0 to a larger value.

Edit following comment about DISTINCT results

The following modified query also handles duplicate rows using DISTINCT as you requested:

SELECT Operation_type
  FROM (
        SELECT DISTINCT(Operation_type),
               CASE
                 WHEN CHARINDEX('.', Operation_type) > 0 THEN CAST(LEFT(Operation_type, CHARINDEX('.', Operation_type) - 1) AS INT)
                 ELSE 0
               END AS OrderCol
          FROM tableA  
       ) a
  ORDER BY OrderCol

Upvotes: 2

Thom A
Thom A

Reputation: 96044

I would personally do it like this and move the number to a different column, to normalise the data:

WITH VTE AS
    (SELECT YourString
     FROM (VALUES ('1.Administration'),
                  ('13.Legal And Contractual Matters'),
                  ('14.Financial'),
                  ('15.Personnel'),
                  ('16.Procurement Of Materials, Works And Services'),
                  ('21.Business Management'),
                  ('3.Information System')) V (YourString) )
SELECT TRY_CONVERT(int,L.Num) AS Num,
       STUFF(V.YourString, 1, LEN(L.Num) + 1, '') AS YourString
FROM VTE V
     CROSS APPLY (VALUES (LEFT(V.YourString, NULLIF(CHARINDEX('.', V.YourString), 0) - 1))) L (Num)
ORDER BY TRY_CONVERT(int, L.Num);

Upvotes: 0

Related Questions