Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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    ' 
  • 2 whitespaces before 'one'
  • 1 whitespace between 'one' and 'two'
  • 4 whitespaces between 'two' and 'three'
  • 2 tabs after 'three'
  • 1 tab after 'four'

Here is the way I do it :

  1. I delete leading and trailing whitespaces
  2. I delete leading and trailing tabs
  3. I replace both 'whitespaces repeated at least two' and tabs by a sole whitespace

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 ?

Many thanks !

like image 293
wiltomap Avatar asked Sep 18 '14 12:09

wiltomap


People also ask

How do I get rid of double spare in Postgres?

In PostgreSQL, the TRIM() function is used to remove the longest string consisting of spaces or any specified character from a string. By default, the TRIM() function removes all spaces (' ') if not specified explicitly.

How do I get rid of white space in PostgreSQL?

The LTRIM() function removes all characters, spaces by default, from the beginning of a string. The RTRIM() function removes all characters, spaces by default, from the end of a string. The BTRIM function is the combination of the LTRIM() and RTRIM() functions.

How do I remove special characters from PostgreSQL?

Postgresql regexp_replace special charactersSELECT regexp_replace('[email protected]','[^\w]+',''); In the above code, the source is '[email protected]' with the special character @, the pattern is '[^\w]+', which means replacing everything that is not number, digit, underline with the nothing.

How do I replace a character in a string in PostgreSQL?

In PostgreSQL, the REPLACE function is used to search and replace all occurrences of a string with a new one. Syntax: REPLACE(source, old_text, new_text );


1 Answers

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

Or In case of update :

UPDATE table_name SET col_name = trim(regexp_replace(col_name, '\s+', ' ', 'g')); 
like image 155
augustorf Avatar answered Sep 22 '22 03:09

augustorf