IBM DB2 SQL SPLIT 分割字符串

--字符串‘ab cd ab'  按空格 分开为ab cd ab

--select * from table (fc_splitstr(‘ab cd ab' ,' '));

/* 得到结果

ab

cb

ab

*/

-- Start of generated script for 10.1.3.12-fisprc-FQIPDB (fqip)
--  Aug-02-2013 at 14:51:20

SET SCHEMA FQIP;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","FQIP";

CREATE FUNCTION "FQIP"."FC_SPLITSTR"
 ("SRCSQL" VARCHAR(8000),  --字符串
  "SPT" VARCHAR(100)  --空格
 )
  RETURNS TABLE
   ("RE" VARCHAR(800)
   )
  SPECIFIC "FQIP"."SQL130801155011000"
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  BEGIN ATOMIC

    return with n(str, ori, pos) as (
      values (srcsql||spt, 1,locate(spt,srcsql||spt))
      union all
      select str, pos+length(spt), locate(spt, str, pos+length(spt))
        from n
        where locate(spt, str,pos+length(spt))>0)
      select substr(str, ori, pos-ori) as result
        from n;
  END;

 

 

 

 

--使用案例

-- Start of generated script for 10.1.3.12-fisprc-FQIPDB (fqip)
--  Aug-02-2013 at 10:15:01

DROP  PROCEDURE "COGNOSTEST2";

#SYNC 10;

SET SCHEMA FQIP    ;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","FQIP";

CREATE PROCEDURE "FQIP"."COGNOSTEST2"
 (IN "INDATA" VARCHAR(100),
 OUT "PO_S_STMT" VARCHAR(32672)
 )
  --SPECIFIC "FQIP"."SQL130801160546200"
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  BEGIN 
declare SQLCODE INT DEFAULT 0;
  DECLARE SQLSTATE CHAR(5);

DECLARE new_culown varchar(4000);
 DECLARE new_case varchar(4000);
DECLARE last_case varchar(4000);
DECLARE newsql varchar(4000);
  DECLARE  v_iquestionid varchar(200);
   DECLARE  v_ianswerid varchar(200);
  DECLARE  at_end  INT  DEFAULT  0; 
 DECLARE  at_end2  INT  DEFAULT  0; 
DECLARE　v_Q10049　varchar(200);
DECLARE　v_VALUE　varchar(200);
 DECLARE　c_VALUE　varchar(200);
   DECLARE  Stmt varchar(4000) DEFAULT '';--参数SQL
 DECLARE  Stmt0 varchar(4000) DEFAULT '';--参数SQL
    DECLARE  IN1 varchar(4000) DEFAULT '';
   --  DECLARE  IN2 varchar(4000) DEFAULT '';
 --SET IN1=INDATA;
 
    DECLARE  C1  CURSOR  FOR    S;
  
 DECLARE  C2  CURSOR  FOR select * from table (fc_splitstr(v_VALUE,','));
   
   DECLARE v_cs_getrptdata  cursor with return for v_st;--返回结果集
 

 set new_case='';
       
    
  -- set newsql = 'DELETE FROM  TEST ';
           --EXECUTE immediate  newsql;
 
 set Stmt0='SELECT '|| INDATA||' FROM MAN_DC_FACT where '|| INDATA||' <> ''0'' ';
  --  values(Stmt) into po_s_Stmt;
 
 prepare S from Stmt0;
-- open v_cs_getrptdata;
 
    
 open C1;
    set at_end = 0;
   while at_end = 0 do
     fetch C1 into v_Q10049;
      if SQLCODE = 0 then   
     
       set v_VALUE=v_Q10049;
        
        open C2　;
       
     
       
              FETCH C2  INTO  c_VALUE;
             WHILE ( SQLSTATE = '00000' ) DO
  
   set newsql = 'insert into TEST VALUES ('||c_VALUE||' )';
           EXECUTE immediate  newsql;
         
             FETCH C2  INTO  c_VALUE;
           END WHILE;

      
                --  PREPARE s1 from newsql;
        --   EXECUTE s1 ;
       
         close c2;
         
 

       
     else
        set at_end = 1;
     end if;
   
   end while;
  close C1;
  
     set Stmt='select V,COUNT(0) COUNT from TEST GROUP BY V ';
    values(Stmt) into po_s_Stmt;
 
 prepare v_st from Stmt;
 open v_cs_getrptdata;
  
END;

#SYNC 20;

 

-- End of generated script for 10.1.3.12-fisprc-FQIPDB (fqip)