求一条 sql 分割数组语句

wwfgu00ing 2012-11-14 09:18:51

有多条记录,每条记录用!连接
单个记录多个字段的值用,连接
比如
declare @para='王二,男!李四,女'
先根据这个参数
拼接成 insert into tab (name,sex) (王二,男)
insert into tab (name,sex) (李四,女)

...全文

188 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

sqlkxr 2012-11-17

打赏
举报

declare @para nvarchar(100) declare @t table (tname varchar(20),tsex varchar(10)) set @para='王二,男!李四,女！李连杰，男' while patindex('%!%',@para)>0 begin declare @all nvarchar(20) set @all=substring(@para,1,patindex('%!%',@para)-1) insert into @t select substring(@all,1,charindex(',',@all)-1),substring(@all,charindex(',',@all)+1,100) set @para=substring(@para,patindex('%!%',@para)+1,200) if patindex('%!%',@para)=0 insert into @t select substring(@para,1,charindex(',',@para)-1),substring(@para,charindex(',',@para)+1,200) end select * from @t

开启时代 2012-11-14

打赏
举报

declare @para varchar(200)='王二,男!李四,女' set @para='insert into tab(name,sex) values('''+replace(replace(@para,',',''','''),'!','''),(''')+''')' exec(@para) select * from tab

發糞塗牆 2012-11-14

打赏
举报

晕，贴错了

use tempdb
 go
 if OBJECT_ID('Tempdb..#tab') is not null
  drop table #tab
 create TABLE #tab(name varchar(4),sex varchar(2))
 go
  declare @para nvarchar(4000);declare @sql nvarchar(4000);
 set @para=N'王二,男!李四,女';
 set @para=REPLACE(@para,',',''',''')
 set @sql='insert into #tab (name,sex) select '''+replace(@para,'!',''' union all select ''')+''''
 exec(@sql)
 select 'insert into tab(name,sex) values('+''''+name+''''+','+''''+sex+'''' +') '
 from #tab
 
 /*
 
 
 ----------------------------------------------
 insert into tab(name,sex) values('王二','男') 
 insert into tab(name,sex) values('李四','女') 
 
 (2 行受影响)
 
 
 */

發糞塗牆 2012-11-14

打赏
举报

use tempdb
 go
 if OBJECT_ID('Tempdb..#tab') is not null
  drop table #tab
 create TABLE #tab(name varchar(4),sex varchar(2))
 go
  declare @para nvarchar(4000);declare @sql nvarchar(4000);
 set @para=N'王二,男!李四,女';
 set @para=REPLACE(@para,',',''',''')
 set @sql='insert into #tab (name,sex) select '''+replace(@para,'!',''' union all select ''')+''''
 exec(@sql)
 select 'insert into tab(name,sex) values('+''''+name+''''+','+''''+sex+'''' +') '
 from #tab
 
 /*
 
 (2 行受影响)
 
 ------------------------------------------
 insert into tab(name,sex) values(王二,男) 
 insert into tab(name,sex) values(李四,女) 
 
 (2 行受影响)
 
 */

發糞塗牆 2012-11-14

打赏
举报

楼主想要这个吧？

use tempdb
 go
 if OBJECT_ID('Tempdb..#tab') is not null
  drop table #tab
 create TABLE #tab(name varchar(4),sex varchar(2))
 go
  declare @para nvarchar(4000);declare @sql nvarchar(4000);
 set @para=N'王二,男!李四,女';
 set @para=REPLACE(@para,',',''',''')
 set @sql='insert into #tab (name,sex) select '''+replace(@para,'!',''' union all select ''')+''''
 exec(@sql)
 select 'insert into tab(name,sex) values('+name+','+sex +') '
 from #tab
 
 /*
 
 (2 行受影响)
 
 ------------------------------------------
 insert into tab(name,sex) values(王二,男) 
 insert into tab(name,sex) values(李四,女) 
 
 (2 行受影响)
 
 */

还在加载中灬 2012-11-14

打赏
举报

use tempdb
go
if OBJECT_ID('Tempdb..#tab') is not null
 drop table #tab
create TABLE #tab(name varchar(4),sex varchar(2))
go
 declare @para nvarchar(4000);declare @sql nvarchar(4000);
set @para=N'王二,男!李四,女';
set @para=REPLACE(@para,',',''',''')
set @sql='insert into #tab (name,sex) select '''+replace(@para,'!',''' union all select ''')+''''
exec(@sql)
select * from #tab