string PL/SQL 中是否有拆分字符串的函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3710589/
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
Is there a function to split a string in PL/SQL?
提问by Sam
I need to write a procedure to normalize a record that have multiple tokens concatenated by one char. I need to obtain these tokens splitting the string and insert each one as a new record in a table. Does Oracle have something like a "split" function?
我需要编写一个过程来规范化具有多个由一个字符连接的标记的记录。我需要获取分割字符串的这些标记，并将每个标记作为新记录插入表中。Oracle 是否有类似“拆分”功能的功能？
采纳答案by RedFilter
You have to roll your own. E.g.,
你必须自己滚动。例如，
/* from :http://www.builderau.com.au/architect/database/soa/Create-functions-to-join-and-split-strings-in-Oracle/0,339024547,339129882,00.htm
select split('foo,bar,zoo') from dual;
select * from table(split('foo,bar,zoo'));
pipelined function is SQL only (no PL/SQL !)
*/
create or replace type split_tbl as table of varchar2(32767);
/
show errors
create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
show errors;
/* An own implementation. */
create or replace function split2(
list in varchar2,
delimiter in varchar2 default ','
) return split_tbl as
splitted split_tbl := split_tbl();
i pls_integer := 0;
list_ varchar2(32767) := list;
begin
loop
i := instr(list_, delimiter);
if i > 0 then
splitted.extend(1);
splitted(splitted.last) := substr(list_, 1, i - 1);
list_ := substr(list_, i + length(delimiter));
else
splitted.extend(1);
splitted(splitted.last) := list_;
return splitted;
end if;
end loop;
end;
/
show errors
declare
got split_tbl;
procedure print(tbl in split_tbl) as
begin
for i in tbl.first .. tbl.last loop
dbms_output.put_line(i || ' = ' || tbl(i));
end loop;
end;
begin
got := split2('foo,bar,zoo');
print(got);
print(split2('1 2 3 4 5', ' '));
end;
/
回答by Tony Andrews
There is apex_util.string_to_table- see my answerto this question.
有apex_util.string_to_table- 请参阅我对这个问题的回答。
Also, prior to the existence of the above function, I once posted a solution here on my blog.
另外，在上述功能存在之前，我曾经在我的博客上发布了一个解决方案。
Update
更新
In later versions of APEX, apex_util.string_to_tableis deprecated, and a similar function apex_string.splitis preferred.
在更高版本的 APEX 中，apex_util.string_to_table已弃用，首选类似的函数apex_string.split。
回答by Frosty Z
If APEX_UTILis not available, you have a solution using REGEXP_SUBSTR().
如果APEX_UTIL不可用，您可以使用REGEXP_SUBSTR().
Inspired from http://nuijten.blogspot.fr/2009/07/splitting-comma-delimited-string-regexp.html:
灵感来自http://nuijten.blogspot.fr/2009/07/splitting-comma-delimited-string-regexp.html：
DECLARE
I INTEGER;
TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
MY_ARRAY T_ARRAY_OF_VARCHAR;
MY_STRING VARCHAR2(2000) := '123,456,abc,def';
BEGIN
FOR CURRENT_ROW IN (
with test as
(select MY_STRING from dual)
select regexp_substr(MY_STRING, '[^,]+', 1, rownum) SPLIT
from test
connect by level <= length (regexp_replace(MY_STRING, '[^,]+')) + 1)
LOOP
DBMS_OUTPUT.PUT_LINE(CURRENT_ROW.SPLIT);
MY_ARRAY(MY_ARRAY.COUNT) := CURRENT_ROW.SPLIT;
END LOOP;
END;
/
回答by Meower68
This only works in Oracle 10G and greater.
这仅适用于 Oracle 10G 及更高版本。
Basically, you use regex_substr to do a split on the string.
基本上，您使用 regex_substr 对字符串进行拆分。
回答by ferralucho
You can use regexp_substr(). Example:
您可以使用 regexp_substr()。例子：
create or replace type splitTable_Type is table of varchar2(100);
declare
l_split_table splitTable_Type;
begin
select
regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level)
bulk collect into
l_split_table
from dual
connect by
regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;
end;
The query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.
查询遍历逗号分隔的字符串，搜索逗号 (,)，然后通过将逗号视为分隔符来拆分字符串。每当遇到分隔符时，它都会将字符串作为一行返回。
levelin statement regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level)refers to a pseudocolumn in Oracle which is used in a hierarchical query to identify the hierarchy level in numeric format: level in connect by
levelin 语句regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level)是指 Oracle 中的伪列，用于分层查询中以数字格式标识层次结构级别：level in connect by
回答by Mo Chahal
You could use a combination of SUBSTR and INSTR as follows :
您可以使用 SUBSTR 和 INSTR 的组合，如下所示：
Example string : field = 'DE124028#@$1048708#@$000#@$536967136#@$'
示例字符串： field = 'DE124028#@$1048708#@$000#@$536967136#@$'
The seperator being #@$.
分隔符是#@$。
To get the '1048708' for example :
例如，获取“1048708”：
If the field is of fixed length ( 7 here ) :
如果该字段是固定长度的（此处为 7）：
substr(field,instr(field,'#@$',1,1)+3,7)
If the field is of variable length :
如果字段长度可变：
substr(field,instr(field,'#@$',1,1)+3,instr(field,'#@$',1,2) - (instr(field,'#@$',1,1)+3))
You should probably look into SUBSTR and INSTR functions for more flexibility.
您可能应该查看 SUBSTR 和 INSTR 函数以获得更大的灵活性。
回答by Emanuele
Please find next an example you may find useful
请找到下一个您可能会觉得有用的示例
--1st substring
--第一个子串
select substr('alfa#bravo#charlie#delta', 1,
instr('alfa#bravo#charlie#delta', '#', 1, 1)-1) from dual;
--2nd substring
--第二个子串
select substr('alfa#bravo#charlie#delta', instr('alfa#bravo#charlie#delta', '#', 1, 1)+1,
instr('alfa#bravo#charlie#delta', '#', 1, 2) - instr('alfa#bravo#charlie#delta', '#', 1, 1) -1) from dual;
--3rd substring
--第三个子串
select substr('alfa#bravo#charlie#delta', instr('alfa#bravo#charlie#delta', '#', 1, 2)+1,
instr('alfa#bravo#charlie#delta', '#', 1, 3) - instr('alfa#bravo#charlie#delta', '#', 1, 2) -1) from dual;
--4th substring
--第4个子串
select substr('alfa#bravo#charlie#delta', instr('alfa#bravo#charlie#delta', '#', 1, 3)+1) from dual;
Best regards
此致
Emanuele
伊曼纽尔
回答by Alain Sollberger
I needed a function that splits a clob and makes sure the function is usable in sql.
我需要一个拆分 clob 并确保该函数在 sql 中可用的函数。
create or replace type vchar_tab is table of varchar2(4000)
/
create or replace function split(
p_list in clob,
p_separator in varchar2 default '|'
) return vchar_tab pipelined is
C_SQL_VCHAR_MAX constant integer:=4000;
C_MAX_AMOUNT constant integer:=28000;
C_SEPARATOR_LEN constant integer:=length(p_separator);
l_amount integer:=C_MAX_AMOUNT;
l_offset integer:=1;
l_buffer varchar2(C_MAX_AMOUNT);
l_list varchar2(32767);
l_index integer;
begin
if p_list is not null then
loop
l_index:=instr(l_list, p_separator);
if l_index > C_SQL_VCHAR_MAX+1 then
raise_application_error(-20000, 'item is too large for sql varchar2: len='||(l_index-1));
elsif l_index > 0 then -- found an item, pipe it
pipe row (substr(l_list, 1, l_index-1));
l_list:=substr(l_list, l_index+C_SEPARATOR_LEN);
elsif length(l_list) > C_SQL_VCHAR_MAX then
raise_application_error(-20001, 'item is too large for sql varchar2: length exceeds '||length(l_list));
elsif l_amount = C_MAX_AMOUNT then -- more to read from the clob
dbms_lob.read(p_list, l_amount, l_offset, l_buffer);
l_list:=l_list||l_buffer;
else -- read through the whole clob
if length(l_list) > 0 then
pipe row (l_list);
end if;
exit;
end if;
end loop;
end if;
return;
exception
when no_data_needed then -- this happens when you don't fetch all records
null;
end;
/
Test:
测试：
select *
from table(split('ASDF|IUYT|KJHG|ASYD'));
回答by Carl
function numinstr(p_source in varchar2,p_token in varchar2)
return pls_integer
is
v_occurrence pls_integer := 1;
v_start pls_integer := 1;
v_loc pls_integer;
begin
v_loc:=instr(p_source, p_token, 1, 1);
while v_loc > 0 loop
v_occurrence := v_occurrence+1;
v_start:=v_loc+1;
v_loc:=instr(p_source, p_token, v_start, 1);
end loop;
return v_occurrence-1;
end numinstr;
--
--
--
--
function get_split_field(p_source in varchar2,p_delim in varchar2,nth pls_integer)
return varchar2
is
v_num_delims pls_integer;
first_pos pls_integer;
final_pos pls_integer;
len_delim pls_integer := length(p_delim);
ret_len pls_integer;
begin
v_num_delims := numinstr(p_source,p_delim);
if nth < 1 or nth > v_num_delims+1 then
return null;
else
if nth = 1 then
first_pos := 1;
else
first_pos := instr(p_source, p_delim, 1, nth-1) + len_delim;
end if;
if nth > v_num_delims then
final_pos := length(p_source);
else
final_pos := instr(p_source, p_delim, 1, nth) - 1;
end if;
ret_len := (final_pos - first_pos) + 1;
return substr(p_source, first_pos, ret_len);
end if;
end get_split_field;
回答by Kishor
There is a simple way folks. Use REPLACE function. Here is an example of comma separated string ready to be passed to IN clause.
有一个简单的方法人。使用替换功能。这是准备传递给 IN 子句的逗号分隔字符串示例。
In PL/SQL:
在 PL/SQL 中：
StatusString := REPLACE('Active,Completed', ',', ''',''');
In SQL Plus:
在 SQL Plus 中：
Select REPLACE('Active,Completed', ',', ''',''') from dual;
