一个有意思的问题，那就是字符串字段内的字符串排序问题，比如有列col,有数据’RDGS’ ,要求输出为’DGRS’。
--测试数据
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 行受影响)
*/
--附录：
;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
责任编辑：小草
