sql实现表内容拆分
ID nick content Forum theTime
1030709200876aaa#bbb#ccc##12012-10-15 17:54:14.000
2030709200876AAA##BBB## 22012-10-16 14:56:32.000
如上图
我要拆分最后结果如下：
ID nick content Forum theTime
3030709200876aaa 12012-10-15 17:54:14.000
4030709200876bbb 12012-10-15 17:54:14.000
5030709200876ccc 12012-10-15 17:54:14.000
6030709200876AAA 22012-10-16 14:56:32.000
7030709200876BBB 22012-10-16 14:56:32.000
请问各位兄弟姐妹，怎么样处理成上述结果，谢谢先了
[解决办法]
declare @t table (id int,nick varchar(20),content varchar(1000),Forum int ,theTime datetime)
insert into @t
select 1 as id, '030709200876' as nick, 'aaa#bbb#ccc##' as content,1 as Forum,'2012-10-15 17:54:14.000' as theTime union all
select 2,'030709200876','AAA##BBB##',2,'2012-10-16 14:56:32.000'
;with t as
(
select id
,nick
,content=replace(content,'##','#')
,Forum
,theTime
from @t
)
,t2 as
(
select nick,content=substring(content,1,charindex('#',content)-1)
,content_split=stuff(content,1,charindex('#',content),'')
,Forum
,theTime
from t
union all
select nick,content=substring(content_split,1,charindex('#',content_split)-1)
,content_split=stuff(content_split,1,charindex('#',content_split),'')
,Forum
,theTime
from t2
where content_split<>''
)
select row_number() over(order by forum,content) as id,nick,content,Forum,theTime
from t2
[解决办法]
create table hp(ID int, nick varchar(14), content varchar(16), Forum int, theTime datetime)insert into hpselect 1, '030709200876', 'aaa#bbb#ccc##', 1, '2012-10-15 17:54:14.000' union allselect 2, '030709200876', 'AAA##BBB##', 2, '2012-10-16 14:56:32.000'select a.ID,a.nick,substring(replace(a.content,'##','#'),b.number,charindex('#',replace(a.content,'##','#'),b.number)-b.number) 'content',a.Forum,a.theTimefrom hp ainner join master.dbo.spt_values bon b.number<=len(replace(a.content,'##','#')) and b.type='P'where substring('#'+replace(a.content,'##','#'),b.number,1)='#'/*ID nick content Forum theTime----------- -------------- ---------- ----------- -----------------------1 030709200876 aaa 1 2012-10-15 17:54:14.0001 030709200876 bbb 1 2012-10-15 17:54:14.0001 030709200876 ccc 1 2012-10-15 17:54:14.0002 030709200876 AAA 2 2012-10-16 14:56:32.0002 030709200876 BBB 2 2012-10-16 14:56:32.000(5 row(s) affected)*/