当前位置：文江博客话题详情

Oracle PL/SQL 中有分割字符串的函数吗？

发布于 2024-09-19 10:21:02 字数 92 浏览 7 评论 0原文

我需要编写一个过程来规范化具有由一个字符连接的多个标记的记录。我需要获取这些标记来分割字符串，并将每个标记作为新记录插入表中。 Oracle有类似“split”的功能吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦巷 2024-09-26 10:21:02

有 apex_util.string_to_table - 请参阅我对此的回答问题。

另外，在上述功能存在之前，我曾经发布过一个解决方案在我的博客上。

更新

在 APEX 的更高版本中，apex_util.string_to_table 已已弃用，以及类似的函数首选 apex_string.split。

回复收藏 0 原文

染墨丶若流云 2024-09-26 10:21:02

如果 APEX_UTIL 不可用，您可以使用 REGEXP_SUBSTR() 解决方案。

灵感来自 http://nuijten.blogspot.fr/2009/07/splitting-逗号分隔字符串-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;
/

If APEX_UTIL is not available, you have a solution using REGEXP_SUBSTR().

Inspired from 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;
/

回复收藏 0 原文

听闻余生 2024-09-26 10:21:02

你必须自己动手。例如，

/* 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;
/

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;
/

回复收藏 0 原文

笑红尘 2024-09-26 10:21:02

您可以使用 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;

查询迭代逗号分隔的字符串，搜索逗号 (,)，然后通过将逗号视为分隔符来分割字符串。每当遇到分隔符时，它都会将字符串作为一行返回。

语句regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level)中的level指的是Oracle中使用的伪列在分层查询中以数字格式标识层次结构级别：连接方式中的级别

You can use regexp_substr(). Example:

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.

level in 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

回复收藏 0 原文

朦胧时间 2024-09-26 10:21:02

这仅适用于 Oracle 10G 及更高版本。

基本上，您使用 regex_substr 对字符串进行分割。

：

已存档链接： http://web. archive.org/web/20170304121704/https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

代码：

select * from emp where ename in (
  select regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) from dual
  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null 
);

This only works in Oracle 10G and greater.

Basically, you use regex_substr to do a split on the string.

https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Edit:

Archived link: http://web.archive.org/web/20170304121704/https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

The code:

select * from emp where ename in (
  select regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) from dual
  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null 
);

回复收藏 0 原文

憧憬巴黎街头的黎明 2024-09-26 10:21:02

您可以使用 SUBSTR 和 INSTR 的组合，如下所示：

示例字符串： field = 'DE124028#@$1048708#@$000#@$536967136#@$'

分隔符为 #@$。

例如，要获取“1048708”：

如果该字段具有固定长度（此处为 7）：

substr(field,instr(field,'#@
如果该字段具有可变长度：
substr(field,instr(field,'#@
您可能应该研究 SUBSTR 和 INSTR 函数以获得更大的灵活性。
,1,1)+3,7)

如果该字段具有可变长度：

您可能应该研究 SUBSTR 和 INSTR 函数以获得更大的灵活性。
,1,1)+3,instr(field,'#@
您可能应该研究 SUBSTR 和 INSTR 函数以获得更大的灵活性。
,1,1)+3,7)

如果该字段具有可变长度：

您可能应该研究 SUBSTR 和 INSTR 函数以获得更大的灵活性。

,1,2) - (instr(field,'#@

您可能应该研究 SUBSTR 和 INSTR 函数以获得更大的灵活性。

,1,1)+3,7)

如果该字段具有可变长度：

您可能应该研究 SUBSTR 和 INSTR 函数以获得更大的灵活性。

,1,1)+3))

您可能应该研究 SUBSTR 和 INSTR 函数以获得更大的灵活性。

,1,1)+3,7)

如果该字段具有可变长度：

您可能应该研究 SUBSTR 和 INSTR 函数以获得更大的灵活性。

You could use a combination of SUBSTR and INSTR as follows :

Example string : field = 'DE124028#@$1048708#@$000#@$536967136#@$'

The seperator being #@$.

To get the '1048708' for example :

If the field is of fixed length ( 7 here ) :

substr(field,instr(field,'#@
If the field is of variable length :
substr(field,instr(field,'#@
You should probably look into SUBSTR and INSTR functions for more flexibility.
,1,1)+3,7)

If the field is of variable length :

You should probably look into SUBSTR and INSTR functions for more flexibility.
,1,1)+3,instr(field,'#@
You should probably look into SUBSTR and INSTR functions for more flexibility.
,1,1)+3,7)

If the field is of variable length :