I am taking data from the users which can be very unreliable. So before saving it in the PostgreSQL DB, I have to clear all the extra character in the beginning and the ending of the string.
Is this Possible to achieve?
' \n \t \n \r\n abc_def_\n xyz \r\n ' to 'abc_def_\n xyz'
Are there any more whitespace like characters that I should care about?
\n newline
\r carriage return
\t horizontal tab
whitespace
I would have thought that \s would cover all whitespace, but that doesn't seem to be the case with Postgres' REGEXP_REPLACE. Instead, I got good mileage using the character class [\r\n\t ] to represent all the whitespace you want to remove. Also, you really want to trim such whitespace only from the beginning and end of the column, and not in middle, so we can search on the following regex pattern:
^[\r\n\t ]*|[\r\n\t ]*$
and then replace with just empty string, to remove it.
WITH yourTable AS (
SELECT ' \n \t \n \r\n abc_def_\n xyz \r\n '::text AS col
)
SELECT
col,
REGEXP_REPLACE(col, '^[\\r\\n\\t ]*|[\\r\\n\\t ]*$', '', 'g') AS col_updated
FROM yourTable;
There's already a function for that. no need to use regex.
select trim( e'\t\n\r\ ' from ' your string ' );