From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Removing whitespace using regexp_replace
Date: Sun, 28 Oct 2007 13:15:20 +0100
Message-ID: <fg1ugo$m6v$1@ger.gmane.org> (raw)
In-Reply-To: <20071028114247.GA1327@KanotixBox>
References: <fg1ra3$ehj$1@ger.gmane.org>
<20071028114247.GA1327@KanotixBox>
Andreas Kretschmer wrote on 28.10.2007 12:42:
>> I have a column with the datatype "text" that may contain leading
>> whitespace (tabs, spaces newlines, ...) and I would like to remove them all
>> (ideally leading and trailing).
>
> You can use trim() for that:
>
> select 'x' || trim(both '\t' from trim(both ' ' from ' \t\tfoo bar ')) || 'x';
>
> (for testing with 'x' around the result)
Yes I was thinking about a solution like that as well, but wouldn't that only
work if the order in which spaces and tabs appear is always the same?
The above would replace ' \t' but not '\t ', right?
> For regexp_replace() you need an extra parameter 'g' like below:
Cool, works like a charm.
Didn't see that parameter when first reading that chapter.
But it seems my problem was actually caused by something else:
SELECT regexp_replace(myfield, '\s*', '', 'g')
FROM mytable;
does not replace anything, but
SELECT regexp_replace(myfield, '[ \t\n\r]*', '', 'g')
FROM mytable;
does replace all whitespaces (as I expected). And subsequently
SELECT regexp_replace(myfield, '^[ \t\n\r]*', '', 'g')
FROM mytable;
replaces only the whitespace at the beginning.
I thought \s is a "shortcut" for "whitespace", which in my understanding is the
same as [ \t\r\n]. Am I wrong here?
Cheers
Thomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Removing whitespace using regexp_replace
In-Reply-To: <fg1ugo$m6v$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox