oracle 如何在 PL/SQL 中解析逗号分隔的字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7028098/
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
How to parse comma delimited string in PL/SQL?
提问by TERACytE
I have a comma delimited string in a PL/SQL script (e.g. data:= 'a,b,c,d,e'), that I need to parse out within the script.
我在 PL/SQL 脚本中有一个逗号分隔的字符串（例如 data:= 'a,b,c,d,e'），我需要在脚本中解析出来。
I would like to loop through the string and process each item. Like a 'foreach' loop.
我想遍历字符串并处理每个项目。就像一个“foreach”循环。
Is this possible in PL/SQL? Can someone point me to some code?
这在 PL/SQL 中可能吗？有人可以指点我一些代码吗？
回答by Tony Andrews
If you are on Oracle 10G or 11G then you should have a built-in Apex function apex_util.string_to_table:
如果您使用的是 Oracle 10G 或 11G，那么您应该有一个内置的 Apex 函数apex_util.string_to_table：
SQL> declare
2 v_array apex_application_global.vc_arr2;
3 v_string varchar2(2000);
4 begin
5
6 -- Convert delimited string to array
7 v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
8 for i in 1..v_array.count
9 loop
10 dbms_output.put_line(v_array(i));
11 end loop;
12
13 -- Convert array to delimited string
14 v_string := apex_util.table_to_string(v_array,'|');
15 dbms_output.put_line(v_string);
16 end;
17 /
alpha
beta
gamma
delta
alpha|beta|gamma|delta
PL/SQL procedure successfully completed.
回答by Julfcar Ali
Oracle 11g:
甲骨文 11g：
SELECT num_value
FROM ( SELECT TRIM (REGEXP_SUBSTR (num_csv,
'[^,]+',
1,
LEVEL))
num_value
FROM ( SELECT '1,2,3,4,5,6,7,8,9,10' num_csv FROM DUAL)
CONNECT BY LEVEL <= regexp_count (num_csv, ',', 1) + 1)
WHERE num_value IS NOT NULL
回答by java.nazif
It is briefliy , simple example following:
简单的例子如下：
declare
string_to_parse varchar2(2000) := 'abc,def,ghi,klmno,pqrst';
l_count number;
l_value varchar2(2000);
begin
string_to_parse := string_to_parse||',';
l_count := length(string_to_parse) - length(replace(string_to_parse,',',''));
-- In oracle 11g use regexp_count to determine l_count
for i in 1 .. l_count loop
select regexp_substr(string_to_parse,'[^,]+',1,i)
into l_value
from dual;
dbms_output.put_line(l_value);
end loop;
end;
回答by Guillaume
A simple tokenizer SQL statement working for Oracle 10g and 11g could be written as follow:
适用于 Oracle 10g 和 11g 的简单分词器 SQL 语句可以编写如下：
WITH string AS (
SELECT pv_string value
FROM dual)
SELECT DISTINCT upper(trim(regexp_substr (value, '[^' || pv_separator || ']+', 1, ROWNUM))) value,
level
FROM string
CONNECT BY LEVEL <= LENGTH(regexp_replace (value, '[^' || pv_separator || ']+')) + 1
ORDER BY level;
Where you can replace pv_stringby the string to parse (e.g. 'a,b,c,d,e') and pv_separatorby the separator string (e.g. ',') .
您可以在其中替换pv_string为要解析的字符串（例如 'a,b,c,d,e'）和pv_separator分隔符字符串（例如 ',') 。
回答by paul3263
For a basic comma delimited string. execute procedure below it is designed to wittle the record down a piece at a time. When p_input_string = p_output_valueit is done. Default is comma but you can pass in a different delimiterparse_delimited_string(V_string, v_value, ';');--- semicolon delimited
对于基本的逗号分隔字符串。下面的执行程序旨在一次减少一段记录。当p_input_string = p_output_value它完成。默认为逗号，但您可以传入不同的分隔符parse_delimited_string(V_string, v_value, ';');--- 分号分隔
create or replace procedure parse_delimited_string(P_INPUT_STRING IN OUT VARCHAR2,
P_OUTPUT_VALUE OUT VARCHAR2,
P_DELIMITOR IN VARCHAR2 DEFAULT ',')
is
/*This Procedure will parse out the first field of a delimited string it will return
the result and a the orginal string minus the parsed out string.
the ideal would be to execute this procedure for each field you want to extract
from string. If you don't know how many values you need to parse out you can just
keep executing this until the p_input_strng equals the P_output_value
*/
begin
IF (instr(P_INPUT_STRING, P_DELIMITOR)) > 0
THEN
P_OUTPUT_VALUE := substr(P_INPUT_STRING, 1, (instr(P_INPUT_STRING, P_DELIMITOR)));
P_INPUT_STRING := regexp_replace(P_INPUT_STRING, P_OUTPUT_VALUE, '',1,1);
P_OUTPUT_VALUE := replace(P_OUTPUT_VALUE, P_DELIMITOR, '');
IF NVL(P_INPUT_STRING, ' ') = ' '
THEN
P_INPUT_STRING := P_OUTPUT_VALUE;
END IF;
ELSE
P_OUTPUT_VALUE := P_INPUT_STRING;
END IF;
end parse_delimited_string;
回答by Ashish sinha
declare
type vartype is varray(10) of number;
x1 vartype;
total integer;
begin
x1 := vartype (1,2,3,4);
total := x1.count;
for i in 1 .. total loop
dbms_output.put_line(x1(i));
end loop;
end;
/
