我正在尝试用regexp_subtr拆分字符串,但我无法使其工作.
所以,首先,我有这个查询
select regexp_substr('Helloworld - test!','[[:space:]]-[[:space:]]') from dual
非常好地提取我的分隔符 – 空白
但是,当我尝试使用此选项拆分字符串时,它只是不起作用.
select regexp_substr('Helloworld - test!','[^[[:space:]]-[[:space:]]]+')from dual
查询什么都不返回.
帮助将不胜感激！
谢谢
解决方法
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 |
