From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Removing whitespace using regexp_replace
Date: Sun, 28 Oct 2007 12:20:36 +0100
Message-ID: <fg1ra3$ehj$1@ger.gmane.org> (raw)
Hi,
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).
I tried
SELECT regexp_replace(myfield, '\A\s*', '')
FROM mytable;
(for leading whitespace, to start with)
But it does not remove anything. I replace my first attempt '^\s*' with '\A\s*'
after reading the chapter about newline-sensitive matching, but that doesn't
seem to do the trick either.
Just for a test I changed this to
SELECT regexp_replace(myfield, '\s*', '')
FROM mytable;
and expected *all* whitespace to be removed from my string, but only the leading
ones were replaced. Which I don't understand at all. Why weren't other
whitespace sequences not replaced with that expression?
What would be the correct RE to replace leading and trailing whitespace without
affecting anything inbetween?
I'm pretty sure I'm missing someting very obvious...
Thanks in advance
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: <fg1ra3$ehj$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