string ORACLE PL-SQL 如何使用函数拆分字符串并返回列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32374326/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE PL-SQL How to SPLIT a string and RETURN the list using a Function
提问by Keshan Fernando
How to Split the given String for the given Delimiter.
如何为给定的分隔符拆分给定的字符串。
Ex:
前任：
INPUT
输入
String => '1,2,3,4,5' Delimiter => ','
字符串 => '1,2,3,4,5' 分隔符 => ','
OUTPUT
输出
1 2 3 4 5
1 2 3 4 5
回答by Gary_W
What about this? The regular expression allows for null list elements too.
那这个呢？正则表达式也允许空列表元素。
SQL> with tbl(str) as (
2 select '1,2,,4,5' from dual
3 )
4 select regexp_substr(str, '(.*?)(,|$)', 1, level, null, 1) element
5 from tbl
6 connect by level <= regexp_count(str, ',')+1;
ELEMENT
--------
1
2
4
5
SQL>
See this post for a function that returns a list element: REGEX to select nth value from a list, allowing for nulls
有关返回列表元素的函数，请参阅此帖子：REGEX to select nth value from a list,allowing for nulls
回答by Keshan Fernando
I have found my own way to split the given String using a FUNCTION
我找到了自己的方法来使用 FUNCTION 拆分给定的字符串
A TYPE should be declared as belows:
TYPE 应声明如下：
TYPE tabsplit IS TABLE OF VARCHAR2 (50)
INDEX BY BINARY_INTEGER;
And the FUNCTION should be written like this:
而 FUNCTION 应该这样写：
FUNCTION fn_split (mp_string IN VARCHAR2, mp_delimiter IN VARCHAR2)
RETURN tabsplit
IS
ml_point NUMBER (5, 0) := 1;
ml_sub_str VARCHAR2 (50);
i NUMBER (5, 0) := 1;
taboutput tabsplit;
ml_count NUMBER (5, 0) := 0;
BEGIN
WHILE i <= LENGTH (mp_string)
LOOP
FOR j IN i .. LENGTH (mp_string)
LOOP
IF SUBSTR (mp_string, j, 1) = mp_delimiter
THEN
ml_sub_str := SUBSTR (mp_string, ml_point, j - ml_point);
ml_point := j + 1;
i := ml_point;
i := i - 1;
taboutput (ml_count) := ml_sub_str;
ml_count := ml_count + 1;
EXIT;
END IF;
END LOOP;
i := i + 1;
END LOOP;
ml_sub_str := SUBSTR (mp_string, ml_point, LENGTH (mp_string));
taboutput (ml_count) := ml_sub_str;
RETURN taboutput;
END fn_split;
This FUNCTION can be used as belows:
此功能可用于以下用途：
DECLARE
taboutput tabsplit;
BEGIN
taboutput := fn_split ('1,2,3,4,5', ',');
FOR i IN 0 .. taboutput.COUNT - 1
LOOP
DBMS_OUTPUT.put_line (taboutput (i));
END LOOP;
END;
