Question

SQL 存储过程 长字符串分割存储

我有一个长字符串(部分)如下:
H,SH600,AAA,123,456,567,566,565,H,SH600,ABC,542,34,35,367,765,H,SH600,AAC,52,314,325,3867,7865
我想把这个字符串分割存入 SH600表中,第一条记录为:AAA,123,456,567,566,565 第二条记录为:ABC,542,34,35,367,765 第三条记录为:AAC,52,314,325,3867,7865 如此类推.字符串的长度很大,记录数能达到5000条.
我有如下一段SQL代码,看大神们能否把它改动一下,
ALTER PROCEDURE [dbo].[AddNewRS]
@STR ----VB 传入的长字符 需要计算一共有多少个H,然后得出要插入多少条记录,再把H后的数据把它分割成 GPDM,GPCN,YDate,Yopen,YHigh 几个字段 分别插入
AS
BEGIN
declare @sql nvarchar(4000)
set @sql ='insert into ' + @BName + ' (GPDM,GPCN,YDate,Yopen,YHigh)
values
(''' + @GPDM +''',''' + @GPCN+ ''','+@YDate+','+@Yopen+','+@YHigh+')'
exec(@sql)

END

Answer 1

declare @inputStr varchar(max)
set @inputStr='H,SH600,AAA,123,456,567,566,565,H,SH600,ABC,542,34,35,367,765,H,SH600,AAC,52,314,325,3867,7865'

declare @divideFlag varchar(20)
set @divideFlag='H,'

declare @bnname varchar(8)
declare @gpdm varchar(8)
declare @gpcn int
declare @ydate int
declare @yopen int
declare @yhigh int

declare @substr varchar(200)
declare @pos1 int
declare @pos2 int

declare @sql varchar(400)

--如果字符串中有分隔符标记
while CHARINDEX(@divideFlag,@inputStr,0)>0
begin
set @pos1 = CHARINDEX(@divideFlag,@inputStr,0)
print '位置1 '+cast(@pos1 as varchar)
set @pos2 = CHARINDEX(@divideFlag,@inputStr,@pos1+1)
print '位置2 ' + cast(@pos2 as varchar)

if @pos2>0
begin
set @substr = SUBSTRING(@inputStr,@pos1,@pos2-@pos1)
print '截取处理串 '+ @substr
set @inputStr = SUBSTRING(@inputStr,@pos2,len(@inputStr))
--print @inputStr
end
else
begin
set @substr = SUBSTRING(@inputStr,@pos1,len(@inputStr))
print '截取处理串 '+ @substr
set @inputStr = 'empty'
--print @inputStr
end
--去掉开头的分割标记
set @substr=substring(@substr,CHARINDEX(@divideFlag,@substr,0)+len(@divideFlag),len(@substr))
print '去掉开头分割标记 ' + @substr

if right(@substr,1)=','
begin
set @substr = left(@substr,len(@substr)-1)
print '去掉末尾的逗号' + @substr
end
--取得表名称
set @bnname = substring(@substr,1,charindex(',',@substr,0)-1)
print @bnname
--取表名称后面的字符串
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr
--取得第一个字段
set @gpdm = substring(@substr,1,charindex(',',@substr,0)-1)
print @gpdm
--取得后面的字符串
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @gpcn = substring(@substr,1,charindex(',',@substr,0)-1)
print @gpcn
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @ydate = substring(@substr,1,charindex(',',@substr,0)-1)
print @ydate
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @yopen = substring(@substr,1,charindex(',',@substr,0)-1)
print @yopen
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @yhigh = substring(@substr,1,charindex(',',@substr,0)-1)
print @yhigh
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr
-- 拼接 插入的 sql 字符串
set @sql = 'insert into '+@bnname+' (gpdm,gpcn,ydate,yopen,yhigh)
values(
'''+@gpdm+''','+cast(@gpcn as varchar)+','+cast(@ydate as varchar)+','+cast(@yopen as varchar)+','+cast(@yhigh as varchar)+'
)'

print @sql
end
-- 拷贝到查询分析器执行，可以看到打印出的消息，拼接的字符串应该可以用