db2 实现split函数解析

问题来源

实现类似功能的函数

WITH n (str, ori, pos) AS
    (VALUES ('192.168.1.10' || '.', 1, locate ('.', '192.168.1.10')) UNION ALL
    SELECT str, pos + length ('.'), locate ('.', str, pos + length ('.'))
    FROM n
    WHERE locate ('.', str, pos + length ('.')) > 0)
SELECT str,ori,pos,substr (str, ori, pos - ori) AS RESULT
FROM n

-----------------------

192.168.1.10.    1    4    192
192.168.1.10.    5    8    168
192.168.1.10.    9    10    1
192.168.1.10.    11    13    10

---

locate函数

locate(arg1,arg2,<pos>) 

 

查找arg2中第一次出现arg1的位置，指定pos，则

从arg2的pos处开始找arg1第一次出现的位置。

posstr函数

posstr(arg1,arg2)查看arg2在arg1中的位置

区别：

posstr中arg2是只能是固定的str，不能使用字段代替，arg1就可以动态生成 

locate中arg1和arg2都可以动态生成。

>>-SUBSTR(<em>string-expression</em>,<em>start</em>-+---------+-)----------------><
                                  '-,<em>length</em>-'     

--------------------------------------

with n(str, ori, pos) as (  
values ('abc,bc,cd,d,ff,', 1, posstr('abc,bc,cd,d,ff,', ','))  
union all   
select str, pos+1, locate(',', str, pos+1)  
from n   
where locate(',', str, pos+1)>0)  
select str, ori, pos, substr(str, ori, pos-ori) as result from n

--
abc,bc,cd,d,ff,    1    4    abc
abc,bc,cd,d,ff,    5    7    bc
abc,bc,cd,d,ff,    8    10    cd
abc,bc,cd,d,ff,    11    12    d
abc,bc,cd,d,ff,    13    15    ff

-------------

函数和存储过程实现

CREATE FUNCTION elemIdx ( pi_str CLOB(64K) ) 
   RETURNS TABLE ( ordinal INTEGER, index INTEGER ) 
   LANGUAGE SQL 
   --DETERMINISTIC 
   NO EXTERNAL ACTION 
   --CONTAINS SQL 
   F1: BEGIN ATOMIC
   RETURN 
      WITH t(ordinal, index) AS 
         ( VALUES ( 0, 0 ) 
           UNION ALL 
           SELECT ordinal+1, COALESCE(NULLIF( 
                     -- find the next delimiter ',' 
                     LOCATE(',', pi_str, index+1), 0), 
                     LENGTH(pi_str)+1) 
           FROM   t 
                  -- to prevent a warning condition for infinite 
                  -- recursions, we add the explicit upper 
                  -- boundary for the &quot;ordinal&quot; values 
           WHERE ordinal < 100 AND 
                  -- terminate if there are no further delimiters 
                  -- remaining 
                  LOCATE(',', pi_str, index+1) <> 0 ) 
      SELECT ordinal, index 
      FROM   t 
      UNION ALL 
      -- add indicator for the end of the string 
      SELECT MAX(ordinal)+1, LENGTH(pi_str)+1 
      FROM   t
      ORDER BY 1; 
    END      
------------------------------------------------------------------------------------------------

CREATE FUNCTION f_split( string CLOB(64K) ) 
   RETURNS TABLE ( elements CLOB(64K) ) 
   LANGUAGE SQL 
   DETERMINISTIC 
   NO EXTERNAL ACTION 
   --CONTAINS SQL 
   F1: BEGIN ATOMIC
   RETURN 
      WITH t(ordinal, index) AS 
         ( SELECT ordinal, index 
           FROM   TABLE ( MCDGLOS.elemIdx(string) ) AS x ) 
      SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1) 
      -- the join below makes sure that we have the lower and 
      -- upper index where we can find each of the ',' delimiters 
      -- that are separating the elements. (For this, we exploit 
      -- the additional indexes pointing to the beginning and end 
      -- of the string.) 
      FROM   t AS t1 JOIN t AS t2 ON 
                ( t2.ordinal = t1.ordinal+1 ) ;
  
END

-------

附带oracle的实现方法

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);  
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)  
RETURN ty_str_split  
IS  
j INT := 0;  
i INT := 1;  
len INT := 0;  
len1 INT := 0;  
str VARCHAR2 (4000);  
str_split ty_str_split := ty_str_split ();  
BEGIN  
len := LENGTH (p_str);  
len1 := LENGTH (p_delimiter);  
WHILE j < len 
LOOP  
j := INSTR (p_str, p_delimiter, i);  
IF j = 0 
THEN  
j := len;  
str := SUBSTR (p_str, i);  
str_split.EXTEND;  
str_split (str_split.COUNT) := str;  
IF i >= len  
THEN  
EXIT;  
END IF;  
ELSE  
str := SUBSTR (p_str, i, j - i);  
i := j + len1;  
str_split.EXTEND;  
str_split (str_split.COUNT) := str;  
END IF;  
END LOOP;  
RETURN str_split;  
END fn_split;  
/