Reputation: 406
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
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:
Upvotes: 2
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
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