Removing whitespace using regexp_replace

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Removing whitespace using regexp_replace
Date: 2007-10-28 11:20:36
Message-ID: fg1ra3$ehj$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Andreas Kretschmer 2007-10-28 11:42:47 Re: Removing whitespace using regexp_replace
Previous Message Chuck D. 2007-10-26 16:29:56 Re: request for help with COPY syntax