如何使用正则表达式使用字符串作为分隔符来拆分字符串? [英] How can I use regex to split a string, using a string as a delimiter?

问题描述

我正在尝试在Oracle存储过程中使用字符串作为分隔符来拆分字符串.我可以轻松地使用instr，但是我正在尝试学习如何使用正则表达式，因为我了解它功能强大且高效.

I'm trying to split a string using a string as a delimiter, in an Oracle store procedure. I can use instr easily, but I'm trying to learn how to do this with regex, as I understand that it is powerful and efficient.

在阅读了一些文章之后，我认为我可以做到这一点(预期结果是"Hello"):

After reading some articles, I thought I could do this (expected result was "Hello"):

select regexp_substr('Hello My Delimiter World', '( My Delimiter )+', 1, 1)
from dual

结果:

我的分隔符

My Delimiter

和(预期结果为"World"):

and (expected result was "World"):

  select regexp_substr('Hello My Delimiter World', '( My Delimiter )+', 1, 2)
    from dual

结果:

空

此要求的正确regex_substr是什么?

What is the correct regex_substr for this requirement?

我正在寻找类似下面的内容.通过一次操作，它将选择字符串中的子字符串:

I'm looking for something like the below. In a single pass, it selects the sub-string within the string:

例如选择regexp_substr('Hello World', '[^ ]+', 1, 2) from dual，但是此示例仅适用于单个字符.

E.g. select regexp_substr('Hello World', '[^ ]+', 1, 2) from dual But this sample only works with a single character.

推荐答案

尝试这些方法.

这将获得您最初要求的第一个元素:

This gets the first element as you originally asked for:

SQL> with tbl(str) as (
      select 'Hello My Delimiter World' from dual
    )
    SELECT REGEXP_SUBSTR( str ,'(.*?)( My Delimiter |$)', 1, 1, NULL, 1 ) AS element
    FROM   tbl;

ELEME
-----
Hello

此版本解析整个字符串.添加了NULL元素以显示它可用于缺少的元素:

This version parses the whole string. NULL elements added to show it works with missing elements:

SQL> with tbl(str) as (
      select ' My Delimiter Hello My Delimiter World My Delimiter  My Delimiter test My Delimiter ' from dual
    )
    SELECT LEVEL AS element,
    REGEXP_SUBSTR( str ,'(.*?)( My Delimiter |$)', 1, LEVEL, NULL, 1 ) AS element_value
    FROM   tbl
    CONNECT BY LEVEL <= regexp_count(str, ' My Delimiter ')+1;

   ELEMENT ELEMENT_VALUE
---------- --------------------
         1
         2 Hello
         3 World
         4
         5 test
         6

6 rows selected.

这篇关于如何使用正则表达式使用字符串作为分隔符来拆分字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！