zgall1 zgall1 - 5 months ago 8
SQL Question

Trimming trailing spaces with PostgresSQL

I have a column

eventDate
which contains trailing spaces. I am trying to remove them with the PostgreSQL function
TRIM
. More specifically I am running



SELECT TRIM(both ' ' from eventDate)
FROM EventDates`




However, the trailing spaces don't go away. Furthermore, when I try and trim another character from the date (such as a number), it doesn't trim either. If I'm reading this correctly - http://www.postgresql.org/docs/current/static/functions-string.html - this should work.

Any thoughts?

Answer

There are many different invisible characters ("white space"). The excellent Wikipedia article about space (punctuation) should give you an idea.

The standard SQL trim() function by default only trims the basic latin space character (Unicode: U+0020 / ASCII 32). Same with the rtrim() and ltrim() variants. Your call also only lists that character (redundantly).

To remove all trailing white space (but not white space inside your string!):

SELECT regexp_replace(eventdate, '\\s+$', '')
FROM   eventdates;

The regular expression explained:
\s .. regular expression class shorthand for [[:space:]]
+ .. 1 or more consecutive matches
$ .. end of string

Demo:

SELECT regexp_replace('inner white   ', '\\s+$', '') || '|'

Returns:

inner white|
Comments