SQL Query - Order by String (which contains number and characters)
I need help with a query sql
that I cannot get to work. I need to order these values by number and then by letter.
Any suggestion how to do this?
I am working on Sql Server 2014
but I think it doesn't matter.
Cod_Turma Turma
1 11-A
2 11-F
3 10-F
4 11-G
5 11-I
6 10-E
7 12-L
8 10-J
9 7-B
10 9-B
11 7-E
12 7-D
13 12-H
The output should be:
Cod_Turma Turma
9 7-B
12 7-D
11 7-E
10 9-B
...
Possible Solution:
SELECT * FROM TableName
ORDER BY CAST(LEFT(Turma, CHARINDEX('-', Turma) - 1) AS INT), --left part
SUBSTRING(Turma, CHARINDEX('-', turma), LEN(turma)) --right part
DECLARE @t table (cod_turma int, turma varchar(10));
INSERT @t values
(1,'11-A')
,(2,'11-F')
,(3,'10-F')
,(4,'11-G')
,(5,'11-I')
,(6,'10-E')
,(7,'12-L')
,(8,'10-J')
,(9,'7-B' )
,(10,'9-B')
,(11,'7-E')
,(12,'7-D')
,(13,'12-H')
SELECT * FROM @t
ORDER BY CAST(LEFT(Turma, CHARINDEX('-', Turma)-1) AS INT), SUBSTRING(turma, CHARINDEX('-', Turma), 1000)
Explanation: Parse Turma into two separate values (int and character); cast the int part to int (so you don't get the ordering 1, 10, 2, 20, 3, 31
) and order it, then order by letter part.
I've edited this to reflect the improvements suggested by @Giorgi Nakeuri's post as well as a comment from @TimSchemlter. (Note: my initial post did not work, I tried to shoot from the hip and was using incorrect syntax.)
Quick and dirty (only for numbers <100 ):
SELECT *
FROM Tbl
ORDER BY REPLICATE(' ', CASE SUBSTRING([turma], 2, 1) WHEN '-' THEN 1 ELSE 0 END)
+ turma
;
but works: DEMO
I think Dan and Giorgi's answers are both good, but for a change, here's an alternative using a little PARSENAME () trick.
SELECT Cod_Turma,
Turma
FROM @yourTable
CROSS APPLY(SELECT REPLACE(Turma,'-','.')) CA(par)
ORDER BY CAST(PARSENAME(par,2) AS INT),PARSENAME(par,1)
Ascending order is sorted first numerically and then alphabetically. select Cod_Turma, Turma from table XX order from Turma asc