SQL查询——按字符串排序(包含数字和字符)

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.

我需要一些sql查询的帮助，但我无法让它工作。我需要按号码顺序排列这些值。

Any suggestion how to do it?

有什么建议吗?

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

我正在研究Sql Server 2014，但我认为这无关紧要。

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

5 个解决方案

#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

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

说明:将Turma解析为两个独立的值(int和字符);将int部分转换为int(这样就不会得到1、10、2、20、3、31的排序)，然后按字母部分排序。

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

我编辑这篇文章是为了反映@Giorgi Nakeuri的文章以及@TimSchemlter的评论所建议的改进。(注意:我最初的帖子没有成功，我试图直接发邮件，使用了一些不正确的语法。)

#3

I think Dan's and Giorgi answers are both good, but for variety's sake, here's an alternative using a little PARSENAME() trick.

我认为Dan和Giorgi的答案都很好，但是为了多样性，这里有一个使用PARSENAME()技巧的替代方案。

SELECT  Cod_Turma,
        Turma
FROM @yourTable
CROSS APPLY(SELECT REPLACE(Turma,'-','.')) CA(par)
ORDER BY CAST(PARSENAME(par,2) AS INT),PARSENAME(par,1)

#4

Quick and dirty (just for numbers < 100):

快速和肮脏(仅适用于小于100的数字):

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

but works: DEMO

但是工作原理:演示

#5

-2

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

按升序排序将先按数字排序，然后按字母排序。选择Cod_Turma, Turma从tableXX订单到Turma asc。

#1