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 '
Here is the way I do it :
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 !
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.
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.
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.
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 );
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'));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With