34,587
社区成员
发帖
与我相关
我的任务
分享
create table exam_MultiProblem(answer varchar(10))
insert into exam_MultiProblem select 'ABC'-- ABC
insert into exam_MultiProblem select 'ABDC'-- BCD
insert into exam_MultiProblem select 'AADC'-- ACD
go
;with cte as(
select answer,1 n,convert(varchar(26),(case when CHARINDEX('A',answer)>0 then 'A' else '' end))newstr from exam_MultiProblem
union all
select a.answer,a.n+1,CONVERT(varchar(26),(case when charindex(char(ascii('A')+a.n),a.answer)>0 then a.newstr+char(ascii('A')+a.n) else a.newstr end)) from cte a where n<26
)select answer,newstr from cte where n=26
/*
answer newstr
---------- --------------------------
AADC ACD
ABDC ABCD
ABC ABC
(3 行受影响)
*/
go
drop table exam_MultiProblem
create table exam_MultiProblem(answer varchar(10))
insert into exam_MultiProblem select 'ABC'-- ABC
insert into exam_MultiProblem select 'ABDC'-- BCD
insert into exam_MultiProblem select 'AADC'-- ACD
go
;WITH T AS(
SELECT top 26 ROW_NUMBER() OVER (ORDER BY ID) AS n FROM SYS.SYSOBJECTS
),T2 AS(
SELECT answer,CHAR(64+N) AS M,N
FROM exam_MultiProblem JOIN T
ON CHARINDEX(CHAR(64+N),answer) > 0
)
SELECT answer,replace(
(SELECT M as [data()] FROM T2 WHERE answer = T3.answer ORDER BY N FOR XML PATH('')
),' ','') AS newstr FROM exam_MultiProblem T3
/*
answer newstr
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABC ABC
ABDC ABCD
AADC ACD
(3 行受影响)
*/
go
drop table exam_MultiProblem