求助:如何在存储过程中拆分字符串

发布网友

我来回答

共2个回答

懂视网

as
 var_tmp varchar2(4000);
 var_element varchar2(4000);
 var_result varchar2(4000);
 var_instr_first number;
 var_instr_second number;
 var_length number;

begin

 var_tmp := var_str;
 var_instr_first :=0;
 var_instr_second :=0;
 var_result :='';
 var_length:=0;

/* 替换掉传过来的特殊字符
chr(9) 制表符
chr(10)回车
chr(13)换行
*/

 var_tmp:= replace(var_tmp,chr(10),'');
 var_tmp:= replace(var_tmp,chr(13),'');
 var_tmp:= replace(var_tmp,chr(9),'');
 
 while instr(var_tmp, ' ') > 0 
 or instr(var_tmp, ',')>0 
 or(var_length>0) loop
 var_instr_first :=instr(var_tmp, ' ');
 var_instr_second :=instr(var_tmp, ',');
 -- dbms_output.put_line('var_instr_kg:'||var_instr_first||' ');
 -- dbms_output.put_line('var_instr_dh:'||var_instr_second||' ');
  var_length:=length(var_tmp);
 -- dbms_output.put_line('var_length :'||var_length||' ');

/* 1 如果是先有空格，比如 12 32,12 32这类 **/

 if var_instr_first<var_instr_second then
  var_element := round(to_number(substr(var_tmp, 1, var_instr_first-1))/3,2);
  var_result := var_result|| var_element|| ' ';
  var_tmp := substr(var_tmp,var_instr_first+1, length(var_tmp));
 -- dbms_output.put_line('var_result kg:'||var_result);
 -- dbms_output.put_line('var_tmp kg:'||var_tmp||' ');
 -- dbms_output.put_line('var_element kg:'||var_element||' ');

/* 2 如果是已经截取完空格，逗号在前面比如 32,12 32这类 **/

 elsif var_instr_first>var_instr_second and var_instr_second>0 then
  var_element := round(to_number(substr(var_tmp, 1, var_instr_second-1))/3,2);
  var_result := var_result || var_element || ',' ;
  var_tmp := substr(var_tmp,var_instr_second+1, length(var_tmp));
  -- dbms_output.put_line('var_result dh:'||var_result);
 -- dbms_output.put_line('var_tmp dh:'||var_tmp||' ');
 -- dbms_output.put_line('var_element dh:'||var_element||' ');

/* 3 如果是已经截取完逗号，已经只剩下最后一个坐标x y，比如12 32这类 **/

 elsif var_instr_first>var_instr_second and var_instr_second=0 then
  var_element := round(to_number(substr(var_tmp, 1, var_instr_first-1))/3,2);
  var_result := var_result|| var_element|| ' ';
  var_tmp := substr(var_tmp,+1, length(var_tmp));
 -- dbms_output.put_line('var_result kg:'||var_result);
 -- dbms_output.put_line('var_tmpvar_instr_first kg:'||var_tmp||' ');
 -- dbms_output.put_line('var_element kg:'||var_element||' ');

/* 4 如果是已经截取到最后一个坐标，比如32这类 **/

 elsif var_instr_first=0 and var_instr_second=0 and var_length>0 then
 -- dbms_output.put_line('var_tmp the last one:'||var_tmp||' ');
 var_element := round(to_number(var_tmp)/3,2);
 var_result := var_result || var_element;
 var_tmp:='';
 -- dbms_output.put_line('var_result 0:'||var_result);
 -- dbms_output.put_line('var_tmp 0:'||var_tmp||' ');
 -- dbms_output.put_line('var_element 0:'||var_element||' ');

/* 5 如果其他的东西，设置成''退出while循环为止 **/

 else 
 var_tmp:='';
 end if;
 -- dbms_output.put_line(' ');
 
 end loop;
 return var_result;
end FN_SPLIT_STR_2;

-- google其他人的拆分function如下：
-- 拆分函数

create or replace function split_str(var_str in varchar2, 
     var_split in varchar2) 
/**************************************************** 
 注意 先执行下面语句 创建类型 
 create or replace type t_ret_table is table of varchar2(100) 
 ** 函数名称：split_str 
 ** 参 数：【名称】  【类型 】 【说明】 
 **  var_str  varchar2 要拆分的字符串 
 **  var_split varchar2 字符串分隔符 
 ** 返 回 值：Result  t_ret_table 拆分后数组集合 
 ** 摘 要：拆分字符串 
 调用 举例： 
 select * from table(split_str('2008-10-21','-')) 
 ****************************************************/ 
 return t_ret_table is 
 var_out t_ret_table; 
 var_tmp varchar2(4000); 
 var_element varchar2(4000);

begin

 var_tmp := var_str; 
 var_out := t_ret_table(); 
 --如果存在匹配的分割符 
 while instr(var_tmp, var_split) > 0 loop 
 var_element := substr(var_tmp, 1, instr(var_tmp, var_split) - 1); 
 var_tmp := substr(var_tmp, 
    instr(var_tmp, var_split) + length(var_split), 
    length(var_tmp)); 
 --var_out.extend(1); 
 var_out.extend; 
 var_out(var_out.count) := var_element; 
 end loop; 
 --var_out.extend(1); 
 var_out.extend; 
 var_out(var_out.count) := var_tmp; 
 return var_out; 
end split_str;

热心网友

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-- 拷贝到查询分析器执行，可以看到打印出的消息，拼接的字符串应该可以用