如何在oracle中拆分包含空格和特殊字符的字符串？

Question

我想用空格和特殊字符(如果有的话)来分割字符串。例如:用于表示移动交换中心(信号强度)。

目前我正在使用正则表达式来拆分字符串，但我无法同时实现空格和特殊字符的拆分。

insert into tmp(word)
    select     regexp_substr('For expressing mobile switching center 
    (signal strength).', '(.*?)([[:space:]]|$)', 1, level, null, 1 ) as token
    from       dual
    connect by level <= regexp_count('For expressing mobile switching center (signal strength).', '[[:space:]/:]+') + 1

CREATE TABLE TMP(WORD VARCHAR2(4000));

Current Output: For
expressing
mobile
switching
center
(signal
strength).

Expected Output: For
expressing
mobile
switching
center
(
signal
strength
)
.

更新代码：

insert into tmp(word)
select     regexp_substr('For expressing mobile switching center (signal strength).', '(.*?)([[:space:]()]|$)', 1, level, null, 1 ) as token
from       dual
connect by level <= regexp_count('For expressing mobile switching center (signal strength).', '(.*?)([[:space:]()]|$)')+ 1

Result:
For
expressing
mobile
switching
center
(null)
signal
strength
.
(null)
(null)

Answer 1

这将拆分为任何非字母或数字的字符：

WITH
    aset AS( SELECT 'abc def;ghi|hkl () 0W-' AS tobesplit FROM DUAL ),
    splitup ( VALUE, rest ) AS
        (SELECT SUBSTR( tobesplit
                      , 1
                      , REGEXP_INSTR( tobesplit || ' ',  '[^a-zA-Z0-9]' ) - 1 )
              , SUBSTR( tobesplit, REGEXP_INSTR( tobesplit || ' ',  '[^a-zA-Z0-9]' ) + 1 ) || ' '
           FROM aset
         UNION ALL
         SELECT SUBSTR( rest
                      , 1
                      , REGEXP_INSTR( rest,  '[^a-zA-Z0-9]' ) - 1 )
              , SUBSTR( rest, REGEXP_INSTR( rest || ' ',  '[^a-zA-Z0-9]' ) + 1 )
           FROM splitup
          WHERE rest IS NOT NULL)
SELECT value 
  FROM splitup where value is not null;

Answer 2

我调整了您的正则表达式，以便它搜索 （a） 括号字符或 （b） 一系列不是空格或括号的字符。

select     regexp_substr('For expressing mobile switching center (signal strength).', '[()]|[^[:space:]()]+', 1, level, null) as token
from       dual
connect by level <= regexp_count('For expressing mobile switching center (signal strength).', '[()]|[^[:space:]()]+');

Answer 3

这是DB2，但希望您可以将其翻译为：

with data (s) as (values
('For expressing mobile switching center (signal strength).')
),
     tally (n) as (
select row_number() over (order by 1)
from   (values (0),(0),(0),(0),(0),(0),(0),(0)) x (n)
cross  join (values (0),(0),(0),(0),(0),(0),(0),(0)) y (n)
)
select regexp_substr(s,'([A-Za-z]+|\(|\)|\.)', 1, n)
from   data
cross  join tally 
where  n <= regexp_count(s,'([A-Za-z]+|\(|\)|\.)')