sql server拆分字符串
创建表create table bb (studentidvarchar(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 no25into aa from bb
--drop table aa
--创建存储过程，动态生成n项
create proc proc_createtablegselectmarksplit
as
if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table .
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
页:
[1]