Clever Geek Handbook

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
...
+3


source to share


5 answers


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
+5


source 


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

+4


source


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

0


source


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


source


Ascending order is sorted first numerically and then alphabetically. select Cod_Turma, Turma from table XX order from Turma asc

-2


source






More articles:


All Articles