526互联

oracle regexp_replace 去空白符

发布时间 2023-06-30 20:41:55作者: 每天进步多一点

1、去空白符SQL：

SELECT  
  regexp_replace(STR,'^\s*','*')  AS "去掉左边空白符",
  regexp_replace(STR,'\s*$','*')  AS "去掉右边空白符",
  regexp_replace(STR,'(^\s*)|(\s*$)','*')  AS "去掉两边空白符"
FROM ( SELECT ' ab1     2cde     ' AS STR FROM DUAL )
--注：
-- 1、把'*'改成''才能真正去空格，这里用'*'号是为了方便理解

结果：

2、 ‘\s’ <==> ‘[[:space:]]’ 表示空白符 因此上面的sql等同于下面：

SELECT  
  regexp_replace(STR,'^[[:space:]]*','*')  AS "去掉左边空白符",
  regexp_replace(STR,'[[:space:]]*$','*')  AS "去掉右边空白符",
  regexp_replace(STR,'(^[[:space:]]*)|([[:space:]]*$)','*')  AS "去掉两边空白符"
FROM ( SELECT ' ab1     2cde     ' AS STR FROM DUAL )

3、空白符
chr(32) 空格