Trim trailing spaces with PostgreSQL
Solution 1
There are many different invisible characters. Many of them have the property WSpace=Y
("whitespace") in Unicode. But some special characters are not considered "whitespace" and still have no visible representation. The excellent Wikipedia articles about space (punctuation) and whitespace characters should give you an idea.
<rant>Unicode sucks in this regard: introducing lots of exotic characters that mainly serve to confuse people.</rant>
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 targets that particular character.
Use regular expressions with regexp_replace()
instead.
Trailing
To remove all trailing white space (but not white space inside the string):
SELECT regexp_replace(eventdate, '\s+$', '') FROM eventdates;
The regular expression explained:
\s
... regular expression class shorthand for [[:space:]]
- which is the set of white-space characters - see limitations below
+
... 1 or more consecutive matches
$
... end of string
Demo:
SELECT regexp_replace('inner white ', '\s+$', '') || '|'
Returns:
inner white|
Yes, that's a single backslash (\
). Details in this related answer:
Leading
To remove all leading white space (but not white space inside the string):
regexp_replace(eventdate, '^\s+', '')
^
.. start of string
Both
To remove both, you can chain above function calls:
regexp_replace(regexp_replace(eventdate, '^\s+', ''), '\s+$', '')
Or you can combine both in a single call with two branches.
Add 'g'
as 4th parameter to replace all matches, not just the first:
regexp_replace(eventdate, '^\s+|\s+$', '', 'g')
But that should typically be faster with substring()
:
substring(eventdate, '\S(?:.*\S)*')
\S
... everything but white space
(?:
re
)
... non-capturing set of parentheses
.*
... any string of 0-n characters
Or one of these:
substring(eventdate, '^\s*(.*\S)')
substring(eventdate, '(\S.*\S)') -- only works for 2+ printing characters
(
re
)
... Capturing set of parentheses
Effectively takes the first non-whitespace character and everything up to the last non-whitespace character if available.
Whitespace?
There are a few more related characters which are not classified as "whitespace" in Unicode - so not contained in the character class [[:space:]]
.
These print as invisible glyphs in pgAdmin for me: "mongolian vowel", "zero width space", "zero width non-joiner", "zero width joiner":
SELECT E'\u180e', E'\u200B', E'\u200C', E'\u200D';
'' | '' | '' | ''
Two more, printing as visible glyphs in pgAdmin, but invisible in my browser: "word joiner", "zero width non-breaking space":
SELECT E'\u2060', E'\uFEFF';
'' | ''
Ultimately, whether characters are rendered invisible or not also depends on the font used for display.
To remove all of these as well, replace '\s'
with '[\s\u180e\u200B\u200C\u200D\u2060\uFEFF]'
or '[\s]'
(note trailing invisible characters!).
Example, instead of:
regexp_replace(eventdate, '\s+$', '')
use:
regexp_replace(eventdate, '[\s\u180e\u200B\u200C\u200D\u2060\uFEFF]+$', '')
or:
regexp_replace(eventdate, '[\s]+$', '') -- note invisible characters
Limitations
There is also the Posix character class [[:graph:]]
supposed to represent "visible characters". Example:
substring(eventdate, '([[:graph:]].*[[:graph:]])')
It works reliably for ASCII characters in every setup (where it boils down to [\x21-\x7E]
), but beyond that you currently (incl. pg 10) depend on information provided by the underlying OS (to define ctype
) and possibly locale settings.
Strictly speaking, that's the case for every reference to a character class, but there seems to be more disagreement with the less commonly used ones like graph. But you may have to add more characters to the character class [[:space:]]
(shorthand \s
) to catch all whitespace characters. Like: \u2007
, \u202f
and \u00a0
seem to also be missing for @XiCoN JFS.
Within a bracket expression, the name of a character class enclosed in
[:
and:]
stands for the list of all characters belonging to that class. Standard character class names are:alnum
,alpha
,blank
,cntrl
,digit
,graph
,lower
,punct
,space
,upper
,xdigit
. These stand for the character classes defined in ctype. A locale can provide others.
Bold emphasis mine.
Also note this limitation that was fixed with Postgres 10:
Fix regular expressions' character class handling for large character codes, particularly Unicode characters above
U+7FF
(Tom Lane)Previously, such characters were never recognized as belonging to locale-dependent character classes such as
[[:alpha:]]
.
Solution 2
It should work the way you're handling it, but it's hard to say without knowing the specific string.
If you're only trimming leading spaces, you might want to use the more concise form:
SELECT RTRIM(eventDate)
FROM EventDates;
This is a little test to show you that it works. Tell us if it works out!
Solution 3
If your whitespace is more than just the space
meta value than you will need to use regexp_replace
:
SELECT '(' || REGEXP_REPLACE(eventDate, E'[[:space:]]', '', 'g') || ')'
FROM EventDates;
In the above example I am bounding the return value in (
and )
just so you can easily see that the regex replace is working in a psql prompt. So you'll want to remove those in your code.
Related videos on Youtube
![zgall1](https://i.stack.imgur.com/kw6v2.jpg?s=256&g=1)
zgall1
Updated on July 09, 2022Comments
-
zgall1 almost 2 years
I have a column
eventDate
which contains trailing spaces. I am trying to remove them with the PostgreSQL functionTRIM()
. 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 the manual correctly this should work. Any thoughts?
-
Cody Caughlan over 10 yearsAre you sure its actually a
space
character and not some other non-visible whitespace character(s)? -
zgall1 over 10 years@CodyCaughlan You're correct. It was some other non-visible whitespace character.
-
Saurabh Tiwari over 2 yearshow did u fix this non-visible character
-
-
Erwin Brandstetter over 10 yearsThis wouldn't stop at trailing spaces, but kill all white space characters in the string.
-
heliotrope about 7 yearsso...would
SELECT regexp_replace(regexp_replace(eventdate, '^\s+', ''), '\s+$', '') FROM eventdates;
work to strip all leading and trailing spaces? -
Erwin Brandstetter about 7 years@heliotrope; If it's just "spaces" use
trim()
. Else, consider the added bits above. -
Nicke Manarin over 6 years@ErwinBrandstetter There's an extra closing parenthesis on the last SQL query.
-
Erwin Brandstetter over 6 years@NickeManarin: Thanks, fixed.
-
XiCoN JFS over 6 yearsfor trimming Non-breaking spaces as well (for an UTF-8 encoded PostreSQL-DB), do
SELECT regexp_replace(regexp_replace(eventdate, '^(\s|\u00a0|\ufeff|\u2007|\u180e|\u202f)+', ''), '(\s|\u00a0|\ufeff|\u2007|\u180e|\u202f)+$', '') FROM eventdates;
-
Erwin Brandstetter over 6 years@XiCoNJFS: Good point. But
\u00a0
is already contained in\s
and you can combine all in a single character class. I updated to clarify some more. -
XiCoN JFS over 6 years@ErwinBrandstetter I'm running PostgreSQL 9.5.11 and
\s
did not catch\u00a0
, that's why I added it to the regex. Thanks for clarifying this great answer! -
Erwin Brandstetter over 6 years@XiCoNJFS: I see. Like I mentioned in the added last chapter Limitations, the definition of character classes ultimately depends on the definition of ctype provided by the server OS and the Postgres version in use. A current OS (preferably POSIX-compliant like Linux) with a current version of Postgres should serve best.
-
Pyrocks almost 3 years@ErwinBrandstetter
substring(eventdate, '^\s*(.*\S)')
andsubstring(eventdate, '(\S.*\S)')
are not the same - the latter doesn't work if there's only a single character (returns NULL) -
Erwin Brandstetter almost 3 years@Pyrocks: Thanks! I added a note to that variant.