zgall1 zgall1 - 1 year ago 81
SQL Question

Trimming trailing spaces with PostgresSQL

I have a column

which contains trailing spaces. I am trying to remove them with the PostgreSQL function
. 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 Source

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


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


inner white|
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download