字符串问题
字符串
格式: 处方号;+数量,处方号;+数量,处方号;+数量,处方号;+数量,处方号;+数量.......
如:1;100,2;200,3;300
请问如何将处方号和数量插入到一个表中
表 处方号 数量
1 100
2 200
3 300
问题点数：50、回复次数：5Top
1 楼txlicenhe（马可）回复于 2003-09-03 12:11:07 得分 30
declare @a varchar(100)
set @a = '1;100,2;200,3;300'
declare @sql varchar(2000)
set @sql = 'select ' + replace(@a,',',' union all select ')
set @sql = replace(@sql,';',',')
select @sql
-- 此时@sql = select 1,100 union all select 2,200 union all select 3,300
exec (' insert 表(处方号，数量) ' + @sql)
Top
2 楼tj_dns（愉快的登山者）回复于 2003-09-03 12:20:27 得分 10
declare @s varchar(8000)
set @s = '1;100,2;200,3;300'
select top 8000 identity(int,1,1) id into #t from sysobjects a,sysobjects b
select 处方号, 数量 from (
select substring(','+@s, id+1, charindex(';',','+@s+',',id+1)-id-1) [处方号],
substring(','+@s, charindex(';',','+@s+',',id+1)+1, charindex(',',','+@s+',',id+1)-charindex(';',','+@s+',',id+1)-1) [数量]
from #t
where substring(','+@s,id,8000) like ',%') A
drop table #tTop
3 楼zhbname（赤澜）回复于 2003-09-03 16:26:32 得分 0
行
Top
4 楼CrazyFor（冬眠的鼹鼠）回复于 2003-09-03 17:00:57 得分 10
参考:
--N要连继，
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c
---------------------------------------------------------------------
declare @a table (id int,string varchar(8000))
insert @a select 1 ,'a,b,c,sd,dfsdfg'
union select 2, 'a,n,sdf,we,t'
union select 3, 's,df,df'
select a.*,b.*,id,substring(','+string+',',N+1,charindex(',',','+string+',',N+1)-(N+1))
from @a a,numtab b
where substring(','+string+',',N,8000) like ',_%'
order by id,NTop
5 楼zhouzdsoft（海潮）回复于 2003-09-03 18:14:16 得分 0
有无更好的方式Top
