SQL Query - Order by String (which contains number and chars)

Question

I need some help with a sql query which I can't get it work. I need to order these values by number then by letter.

Any suggestion how to do it?

I am working on Sql Server 2014, but I think it's irrelevant.

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

Output should be:

Cod_Turma   Turma
9           7-B
12          7-D
11          7-E
10          9-B
...

Answer 1

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

Answer 2

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 out into two separate values (the int and the character); cast the int part to int (so that you don't get an ordering of 1, 10, 2, 20, 3, 31) and order by that, then order by the letter part.

I've edited this to reflect improvements suggested by @Giorgi Nakeuri's post as well as a comment by @TimSchemlter. (Note: my initial post did not work, I tried to fire from the hip and used some incorrect syntax.)

Answer 3

Quick and dirty (just for numbers < 100):

SELECT *
FROM Tbl
ORDER BY REPLICATE(' ', CASE SUBSTRING([turma], 2, 1) WHEN '-' THEN 1 ELSE 0 END) 
         + turma
;

but works: DEMO

Answer 4

I think Dan's and Giorgi answers are both good, but for variety's sake, 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)

Answer 5

Order by ascending order will sort by numbers first and then alphabets. select Cod_Turma, Turma from tableXX order by Turma asc