我如何排序Varchar值..请帮助 [英] How I Can Sort Varchar Values..Pls Help
本文介绍了我如何排序Varchar值..请帮助的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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屋!
查看全文