Skip site navigation (1) Skip section navigation (2)

Re: Removing whitespace using regexp_replace

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Removing whitespace using regexp_replace
Date: 2007-10-28 12:15:20
Message-ID: fg1ugo$m6v$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-sql
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




In response to

Responses

pgsql-sql by date

Next:From: Andreas KretschmerDate: 2007-10-28 12:32:54
Subject: Re: Removing whitespace using regexp_replace
Previous:From: Andreas KretschmerDate: 2007-10-28 11:42:47
Subject: Re: Removing whitespace using regexp_replace

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group