sql server拆分字符串

创建表
create table bb (studentid  varchar(100),markstr varchar(50))
insert into bb select
'2002081108','AAAAAAAAAAAAAAAAAAAAAAAAA' union all select
'2002081107','BBBBBBBBBBBBBBBBBBBBBBBBB' union all select
'2002081109','CCCCCCCCCCCCCCCCCCCCCCCCC' union all select
'2002081110','DDDDDDDDDDDDDDDDDDCDCDDCC' union all select
'2002081111','DDDEEDDDDDDDDDDEDDCDCDDCC'
---笨办法，写死的长度为25
select studentid,substring(markstr,1,1) as no1,substring(markstr,2,1) as no2,substring(markstr,3,1) as no3,
substring(markstr,4,1) as no4,substring(markstr,5,1) as no5,substring(markstr,6,1) as no6,substring(markstr,7,1) as no7,
substring(markstr,8,1) as no8,substring(markstr,9,1) as no9,substring(markstr,10,1) as no10,substring(markstr,11,1) as no11,
substring(markstr,12,1) as no12,substring(markstr,13,1) as no13,substring(markstr,14,1) as no14,substring(markstr,15,1) as no15,
substring(markstr,16,1) as no16,substring(markstr,17,1) as no17,substring(markstr,18,1) as no18,substring(markstr,19,1) as no19,
substring(markstr,20,1) as no20,substring(markstr,21,1) as no21,substring(markstr,22,1) as no22,substring(markstr,23,1) as no23,
substring(markstr,24,1) as no24,substring(markstr,25,1) as no25  into aa from bb

--drop table aa
--创建存储过程，动态生成n项
create proc proc_createtablegselectmarksplit
as
if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[tablegselectmarksplit]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)  
  drop   table   [dbo].[tablegselectmarksplit]
declare @i int
declare @t int
declare @sql varchar(8000)
set @sql=''
set @t=1
select @i=max(len(markstr)) from bb
while(@t<=@i)
begin
set @sql=@sql+',substring(markstr,'+cast(@t as varchar)+',1) as no'+cast(@t as varchar)
set @t=@t+1
end
set @sql= 'select studentid,'+stuff(@sql,1,1,'')+' into tablegselectmarksplit from bb '
exec(@sql)
--查询拆分完的结果表
select * from tablegselectmarksplit