问题来源
实现类似功能的函数
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函数
posstr函数
posstr(arg1,arg2)查看arg2在arg1中的位置
区别:
>>-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 "ordinal" 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;
/