To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Removing whitespace using regexp_replace
Date: Sun, 28 Oct 2007 12:20:36 +0100
Lines: 34
Message-ID: <fg1ra3$ehj$1@ger.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.6) Gecko/20070728 Thunderbird/2.0.0.6 Mnenhy/0.7.5.666

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