22,209
社区成员
发帖
与我相关
我的任务
分享
/*编号 内容 新内容
---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
001 ACD ACD
002 BA AB
004 CA AC
003 DCB BCD
(4 行受影响)
*/
if object_id('tb')is not null drop table tb
go
CREATE TABLE tb(编号 varchar(10), 内容 varchar(10))
INSERT tb SELECT
'001' , 'ACD' UNION ALL SELECT
'002' , 'BA' UNION ALL SELECT
'003' , 'DCB' UNION ALL SELECT
'004' , 'CA'
;
with cte1 as
(
select * from
(select char(Ascii('A')+number) col from master..spt_values where type='p' and number<26) tmp
join tb
on charindex(col,内容)>0
)
select 编号 ,内容,新内容=(select ''+col from cte1 where 编号=c.编号 for xml path(''))
from cte1 c
group by 编号,内容
if object_id('tb')is not null drop table tb
go
CREATE TABLE tb(编号 varchar(10), 内容 varchar(10))
INSERT tb SELECT
'001' , 'ACD' UNION ALL SELECT
'002' , 'BA' UNION ALL SELECT
'003' , 'DCB' UNION ALL SELECT
'004' , 'CA'
if object_id('f_str')is not null drop function f_str
go
create function f_str(@id varchar(10))
returns varchar(100)
as
begin
declare @tc TABLE(编号 varchar(10), 内容 varchar(10))
declare @s varchar(400),@n int,@str varchar(10)
set @n=1
set @str=(select 内容 from tb where 编号=@id)
while @n<=len(@str)
begin
insert @tc select @id,substring(@str,@n,1)
set @n=@n+1
end
select @s=isnull(@s,'')+内容 from @tc where 编号=@id order by ascii(内容)
return @s
end
go
select 编号,内容=dbo.f_str(编号) from tb
编号 内容
---------- ----------------------------------------------------------------------------------------------------
001 ACD
002 AB
003 BCD
004 AC
(4 行受影响)
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (编号 varchar(3),内容 varchar(3))
insert into #T
select '001','ACD' union all
select '002','BA' union all
select '003','DCB' union all
select '004','CA'
create function fn_sort(@str varchar(7000))
returns varchar(7000)
as
begin
declare @t table(col varchar(2))
declare @s varchar(7000)
while LEN(@str)>0
begin
insert into @t select left(@str,1)
set @str=stuff(@str,1,1,'')
end
select @s=isnull(@s+'','')+col from @t order by col
return @s
end
select 编号,dbo.fn_sort(内容) as 内容 from #T
/*
编号 内容
001 ACD
002 AB
003 BCD
004 AC
*/
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-27 14:10:01
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (编号 varchar(3),内容 varchar(3))
INSERT INTO @T
SELECT '001','ACD' UNION ALL
SELECT '002','BA' UNION ALL
SELECT '003','DCB' UNION ALL
SELECT '004','CA'
--SQL查询如下:
SELECT *
FROM @T AS A
CROSS APPLY
( SELECT 新内容 = (SELECT v AS [text()]
FROM (SELECT SUBSTRING(A.内容,number,1) AS v
FROM master.dbo.spt_values
WHERE type = 'p' AND SUBSTRING(A.内容,number,1) <> '') AS T
ORDER BY v FOR XML PATH(''),TYPE).value('.','varchar(10)')) AS B
/*
编号 内容 新内容
---- ---- ----------
001 ACD ACD
002 BA AB
003 DCB BCD
004 CA AC
(4 行受影响)
*/
--还是写个函数,最好的方法!
--取子串CASE语句搞定
CASE语句搞定