PostgreSQL regexp_replace（）仅保留一个空格 [英] PostgreSQL regexp_replace() to keep just one whitespace

问题描述

我需要清理在字符串的开头或结尾处都包含空格和制表符的字符串列（真是一团糟！）。我想每个单词之间只保留一个空格。假设我们有以下包含所有可能情况的字符串：

I need to clean up a string column with both whitespaces and tabs included within, at the beginning or at the end of strings (it's a mess !). I want to keep just one whitespace between each word. Say we have the following string that includes every possible situation :

mystring = '  one two    three      four    '

• 'one'之前的2个空格


• 之间的1个空格一个和两个


• 两个和三个之间的4个空格


• 在三个之后的两个制表符


• 四个之后的1个标签页


• 2 whitespaces before 'one'
• 1 whitespace between 'one' and 'two'
• 4 whitespaces between 'two' and 'three'
• 2 tabs after 'three'
• 1 tab after 'four'

这是我的操作方式：



1. 我删除开头和结尾的空格


2. 我删除开头和结尾的制表符


3. I用唯一的空格替换两个至少重复两次的空格和制表符



WITH

  t1 AS (SELECT'  one two    three      four    '::TEXT AS mystring),

  t2 AS (SELECT TRIM(both ' ' from mystring) AS mystring FROM t1),

  t3 AS (SELECT TRIM(both '\t' from mystring) AS mystring FROM t2)

  SELECT regexp_replace(mystring, '(( ){2,}|\t+)', ' ', 'g') FROM t3 ;

我最终得到以下字符串，看起来不错，但我仍然有尾随空格...

I eventually get the following string, which looks nice but I still have a trailing whitespace...

'one two three four '

有任何以更简单的方式来解决最后一个问题的想法吗？

Any idea on doing it in a more simple way and solving this last issue ?

非常感谢！

推荐答案

SELECT trim(regexp_replace(col_name, '\s+', ' ', 'g')) as col_name FROM table_name;

或者在更新的情况下：

UPDATE table_name SET col_name = trim(regexp_replace(col_name, '\s+', ' ', 'g'));

这篇关于PostgreSQL regexp_replace（）仅保留一个空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！