如何替换所有的空格为一个空格呢？

Question

with t as(
     select '01023454955            1381111111    1234443456' phone from dual union all
     select '01023445555             04133933934' from dual)
select regexp_replace(phone,'[[:blank:]]+','|') phone
from t
/
PHONE
--------------------------------------------------------------------------------
01023454955|1381111111|1234443456
01023445555|04133933934

Answer 1

SQL> with t as(
  2       select '5409505     \                  580480' phone from dual union all
  3       select '5409505 、      405098' from dual)
  4  select regexp_replace(phone,
  5                       '[[:blank:]]+([[:punct:]]|\、)[[:blank:]]+','|') phone
  6  from t
  7  /
 
PHONE
--------------------------------------------------------------------------------
5409505|580480
5409505|405098

Answer 2

with t as(
     select '5409505     \      580480' phone from dual union all
     select '5409505   "\,;、，。:：+-*/&%$#@!      405098' from dual)--但是这里最好不出现正则表达式的元字符
select regexp_replace(phone,
                     '[[:blank:]]+[[:graph:]]+[[:blank:]]+','|') phone
from t
/
PHONE
--------------------------------------------------------------------------------
5409505|580480
5409505|405098
正则表达式元字符

Answer 3

select regexp_replace('abc    def','\s+','|') from dual;
select regexp_replace('abc    def','[ ]+','|') from dual;