求一SQL, 字段字符串排序

[ghost5216]

表TAB中有一字符串字段A

A 中存储的是数字组合
比如: 0302
21345
213567
31123
即可能存在相同的数字,长度不固定

怎么select 出A的结果集是排序好的
即
0023
12345
123567
11233

求帮助

谢谢

[在路上_-]

这样不行吗？

select A from TAB order by A

[在路上_-]

对不起看错了，是字符串里的字符排序呀

[在路上_-]

http://blog.csdn.net/happyflystone/article/details/3819863

[HelloWordGirl]

;WITH cte AS ( SELECT TOP(20) ROW_NUMBER() OVER(ORDER BY [object_id]) AS ID FROM sys.objects ), cte1 AS ( SELECT A.A, B.ID, SUBSTRING(A.A,B.ID,1) AS v FROM Tab AS A JOIN cte AS B ON SUBSTRING(A.A,B.ID,1)<>'' ) SELECT A, REPLACE( ( SELECT v AS [data()] FROM cte1 WHERE A=A.A ORDER BY v FOR XML PATH('') ),' ','') FROM Tab AS A

[lzw_0736]

WITH a0 (msg) AS
(
SELECT '0302' UNION ALL
SELECT '21345' UNION ALL
SELECT '213567' UNION ALL
SELECT '31123'
)
,a1 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY @@servername) id,*
FROM a0
)
,a2 AS
(
SELECT id,msg,SUBSTRING(msg,1,1) msgb,1 n FROM a1
UNION ALL
SELECT id,msg,SUBSTRING(msg,n+1,1),n+1 FROM a2
WHERE n+1<=LEN(msg)
)
--SELECT * FROM a2 ORDER BY 1

SELECT msg=(SELECT ''+msgb FROM a2 WHERE id=a.id ORDER BY msgb FOR XML PATH(''))
FROM a2 a
GROUP BY id
OPTION(MAXRECURSION 0)

[xiaodongni]

0023 12345 123567 11233 这个按照啥排序的。 如果是安装每一个字符。 应该11233 在1234567 前面啊 也在12345 前面啊

[xiaodongni]

知道了 看懂了

[wtujedp]

/*排序函数*/ create function F_NUM_ORDER ( @str varchar(100) ) returns varchar(100) as begin declare @sStr varchar(1);--当前取字符 declare @nStr varchar(1);--当前字符的下一个 declare @i int; declare @j int; declare @len int; set @len=LEN(@str); if(@len>0) begin set @i=1; set @j=1; while @i<=@len begin while @j<=@len-@i-- begin set @sStr=SUBSTRING(@str,@j,1); set @nStr=SUBSTRING(@str,@j+1,1); if @sStr>@nStr begin set @str=STUFF( STUFF(@str,@j,1,@nStr),@j+1,1,@sStr); end set @j+=1; end set @i+=1; set @j=0; end end return @str; end /*创建测试表*/ create table t ( a varchar(100) ) /*插入测试数据*/ insert into t values('0302 ') insert into t values('21345') insert into t values('213567') insert into t values('31123') /*使用函数查询需要排序的字段*/ select dbo.F_NUM_ORDER (a)from t /*查询结果*/ 0023 12345 123567 11233