db2如何使用存储过程拆分字符串

发布网友发布时间：2022-04-26 13:28

共1个回答

热心网友时间：2023-10-31 18:21

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 intdeclare @ydate intdeclare @yopen intdeclare @yhigh intdeclare @substr varchar(200)declare @pos1 intdeclare @pos2 intdeclare @sql varchar(400)--如果字符串中有分隔符标记while CHARINDEX(@divideFlag,@inputStr,0)0beginset @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 @pos20beginset @substr = SUBSTRING(@inputStr,@pos1,@pos2-@pos1)print '截取处理串 '+ @substrset @inputStr = SUBSTRING(@inputStr,@pos2,len(@inputStr))--print @inputStrendelsebeginset @substr = SUBSTRING(@inputStr,@pos1,len(@inputStr))print '截取处理串 '+ @substrset @inputStr = 'empty'--print @inputStrend--去掉开头的分割标记set @substr=substring(@substr,CHARINDEX(@divideFlag,@substr,0)+len(@divideFlag),len(@substr))print '去掉开头分割标记 ' + @substrif right(@substr,1)=','beginset @substr = left(@substr,len(@substr)-1)print '去掉末尾的逗号' + @substrend--取得表名称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 @substrset @gpcn = substring(@substr,1,charindex(',',@substr,0)-1)print @gpcnset @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))print @substrset @ydate = substring(@substr,1,charindex(',',@substr,0)-1)print @ydateset @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))print @substrset @yopen = substring(@substr,1,charindex(',',@substr,0)-1)print @yopenset @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))print @substrset @yhigh = substring(@substr,1,charindex(',',@substr,0)-1)print @yhighset @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 @sqlend-- 拷贝到查询分析器执行，可以看到打印出的消息，拼接的字符串应该可以用