我如何排序Varchar值..请帮助 [英] How I Can Sort Varchar Values..Pls Help

问题描述

how i can sort below varchar values 
13028-2DEW1
13028-2DEW10
13028-2DEW2
13028-2DEW3
13028-2DFB1
13028-2DLW1
13028-2DLW10
13028-2DLW101
13028-2DLW2
13028-2DLW7

i need output like this

13028-2DEW1
13028-2DEW2
13028-2DEW3
13028-2DEW10
13028-2DFB1
13028-2DLW1
13028-2DLW2
13028-2DLW7
13028-2DLW10
13028-2DLW101

推荐答案

基本上你不能 - 字符串值的排序顺序总是取决于字符之间的差异，而不是总数值。



但是如果你将结束位转换为数字....

Basically you can't - the sort order for a string value is always determined on the difference between characters, not a "total numeric value".

But if you convert the "end bit" to a number....

SELECT * FROM MyTable
ORDER BY SUBSTRING(MyColumn, 1, 10), CAST(SUBSTRING(MyColumn, 11, 1000) AS INT)

但是 - 您可能最好将其构建为计算列 [ ^ ]并分别存储各个部分 - 这样可以省去很多麻烦！

But - you might be better off constructing this as a Computed Column[^] and storing the various parts separately - it would save a lot of hassle!

查看样本：

Have a look at sample:

DECLARE @tmp TABLE (code VARCHAR(30))

INSERT INTO @tmp (code)
VALUES('13028-2DEW1'),
('13028-2DEW10'),
('13028-2DEW2'),
('13028-2DEW3'),
('13028-2DFB1'),
('13028-2DLW1'),
('13028-2DLW10'),
('13028-2DLW101'),
('13028-2DLW2'),
('13028-2DLW7')


SELECT code, LEFT(code,10) as begpart, CONVERT(INT, RIGHT(code, LEN(code)-10)) AS endpart
FROM @tmp
ORDER BY LEFT(code,10), CONVERT(INT,RIGHT(code, LEN(code)-10))

结果：

Result:

13028-2DEW1	13028-2DEW	1
13028-2DEW2	13028-2DEW	2
13028-2DEW3	13028-2DEW	3
13028-2DEW10	13028-2DEW	10
13028-2DFB1	13028-2DFB	1
13028-2DLW1	13028-2DLW	1
13028-2DLW2	13028-2DLW	2
13028-2DLW7	13028-2DLW	7
13028-2DLW10	13028-2DLW	10
13028-2DLW101	13028-2DLW	101

如果从选择列表中删除 begpart 和 endpart ，您将只看到代码 values。

If you remove begpart and endpart from select list, you'll see only code values.

这篇关于我如何排序Varchar值..请帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！