sql - Oracle regexp 忽略重复字符

Question

我有一个字符串:
hheelllloo wwoorrlldd !!应该返回 hello world!
我对上述的尝试是

SELECT regexp_substr('hheelllloo wwoorrlldd !!', '.', LEVEL*2-1)l_val
FROM dual
CONNECT BY LEVEL <= LENGTH('hheelllloo wwoorrlldd !!')/2;

但它不是我需要的方式，逻辑没有正确使用。
我也试过使用 '(\w)\1'
我在示例数据中的预期结果:

WITH t AS
     ( SELECT 'hheelllloo wwoorrlldd!!' AS word FROM dual
     UNION
     SELECT 'hellow world!' FROM dual
     UNION
     SELECT 'ootthheerrss' FROM dual
     UNION
     SELECT 'ootthheeerrss' FROM dual
     )
SELECT * FROM t;

输出应如下所示:

 hello world!    --expression applied
 hellow world!     -- not needed for non-repeated characters
 others           --expression applied
 otheers          --applied and extra `e` considered as non-repeated.

我可以在一个查询中完成整个查询，还是第一个查询？
提前致谢，这仅用于我的练习并了解不同的逻辑。

Answer 1

您可以使用 regexp_replace()带反向引用的正则表达式函数:

SQL> WITH t1(col) AS (
  2    select 'hheelllloo wwoorrlldd!!' from dual union all
  3    select 'hellow world!'           from dual union all
  4    select 'ootthheerrss'            from dual union all
  5    select 'ootthheeerrss'           from dual
  6  )
  7  select regexp_replace(col, '(.)\1', '\1') as res
  8    from t1
  9  ;

RES
--------------
hello world!
helow world!
others
otheers