SQL2005下字符串字段内的字符排序

SQL2005下字符串字段内的字符排序

今天和梁翁在群里聊天，小家伙突然抛出一个有意思的问题，那就是字符串字段内的字符串排序问题，比如有列col,有数据'RDGS' ,要求输出为'DGRS'。

当时我本想和梁翁讨论一种思路，那山羊闹着要聊天，不许讨论技术问题，所以这夜深时俺整理一下，与大家分享。

-----------------------------------------------------------------------

-- Author: happyflystone

-- Date : 2009-01-17 22:56:11

-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

-- Apr 14 2006 01:12:25

-- Copyright (c) 1988-2005 Microsoft Corporation

-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

--

----------------------------------------------------------------------

--测试数据

DECLARE @T TABLE(COL VARCHAR(10))

INSERT @T SELECT 'WEFSA'

INSERT @T SELECT 'DFSA'

INSERT @T SELECT 'DQWF'

--数据生成

;

WITH T

AS

(

SELECT top 26 ROW_NUMBER() OVER (ORDER BY ID) AS n

FROM SYS.SYSOBJECTS

),

T2

AS

(

SELECT col,CHAR(64+N) AS M,N

FROM @T JOIN T

ON CHARINDEX(CHAR(64+N),COL) > 0

)

SELECT

COL,

replace(

(SELECT M as [data()] FROM T2 WHERE COL = T3.COL ORDER BY N FOR XML PATH('')

),' ','') AS NEWCOL

FROM @T T3

--结果

/*

COL NEWCOL

---------- --------

WEFSA AEFSW

DFSA ADFS

DQWF DFQW

(3 行受影响)

*/

--附录：(小P梁写的，也就是梁翁了，大家自己比吧)

;WITH Numbers AS

(

SELECT TOP(20)

ROW_NUMBER() OVER(ORDER BY [object_id]) AS ID

FROM sys.objects

),

Liang AS

(

SELECT

A.col,

B.ID,

SUBSTRING(A.col,B.ID,1) AS v

FROM @t AS A

JOIN Numbers AS B

ON SUBSTRING(A.col,B.ID,1)<>''

)

SELECT

col,

REPLACE(

(

SELECT

v AS [data()]

FROM Liang

WHERE col=A.col

ORDER BY v

FOR XML PATH('')

),' ','')

FROM @t AS A