Sort alphanumeric SQL Server column

Question

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 *

Store the number separately. Numerical values don't sort in the same order as alphanumerical values. '2' is greater than '10'; as you have found out. — Thom A
– Thom A ♦, Commented Oct 30, 2019 at 10:36

Martin · Accepted Answer · 2019-10-30 10:43:43Z

2

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

edited Oct 30, 2019 at 10:43

answered Oct 30, 2019 at 10:40

Martin

16.5k1 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eshaa Over a year ago

I want to use distinct of that column.

Martin Over a year ago

@eshaa I've updated the answer to also include outputting distinct results only.

Radu Gheorghiu · Accepted Answer · 2019-10-30 10:47:07Z

2

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:

edited Oct 30, 2019 at 10:47

answered Oct 30, 2019 at 10:40

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

2 Comments

eshaa Over a year ago

with select * it works. but I need to get distinct column values.

Radu Gheorghiu Over a year ago

@eshaa updated my answer to solve the distinct "problem"

Thom A · Accepted Answer · 2019-10-30 10:42:34Z

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);

Collectives™ on Stack Overflow

Sort alphanumeric SQL Server column

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related