使用regexp_substr在Oracle中按空格和字符分隔字符串作为定界符 [英] Split string by space and character as delimiter in Oracle with regexp_substr

问题描述

我正在尝试使用regexp_subtr分割字符串，但是我无法使其工作.

I'm trying to split a string with regexp_subtr, but i can't make it work.

所以，首先，我有这个查询

So, first, i have this query

select regexp_substr('Helloworld - test!' ,'[[:space:]]-[[:space:]]') from dual

很好地提取了我的定界符-空白-空白

which very nicely extracts my delimiter - blank-blank

但是，当我尝试使用此选项分割字符串时，它不起作用.

But then, when i try to split the string with this option, it just doesn't work.

select regexp_substr('Helloworld - test!' ,'[^[[:space:]]-[[:space:]]]+')from dual

查询不返回任何内容.

我们将不胜感激！ 谢谢

Help will be much appreciated! Thanks

推荐答案

SQL提琴

Oracle 11g R2架构设置:

CREATE TABLE TEST( str ) AS
          SELECT 'Hello world - test-test! - test' FROM DUAL
UNION ALL SELECT 'Hello world2 - test2 - test-test2' FROM DUAL;

查询1 :

SELECT Str,
       COLUMN_VALUE AS Occurrence,
       REGEXP_SUBSTR( str ,'(.*?)([[:space:]]-[[:space:]]|$)', 1, COLUMN_VALUE, NULL, 1 ) AS split_value
FROM   TEST,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL < REGEXP_COUNT( str ,'(.*?)([[:space:]]-[[:space:]]|$)' )
           )
           AS SYS.ODCINUMBERLIST
         )
       )

结果 :

Results:

|                               STR | OCCURRENCE |  SPLIT_VALUE |
|-----------------------------------|------------|--------------|
|   Hello world - test-test! - test |          1 |  Hello world |
|   Hello world - test-test! - test |          2 |   test-test! |
|   Hello world - test-test! - test |          3 |         test |
| Hello world2 - test2 - test-test2 |          1 | Hello world2 |
| Hello world2 - test2 - test-test2 |          2 |        test2 |
| Hello world2 - test2 - test-test2 |          3 |   test-test2 |

这篇关于使用regexp_substr在Oracle中按空格和字符分隔字符串作为定界符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！