sql分割字符串 oracle,sql – 使用空格和字符将字符串拆分为Oracle中的分隔符和regexp_substr...

SQL Fiddle

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

)

)

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