pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Removing whitespace using regexp_replace 5+ messages / 2 participants [nested] [flat]
* Removing whitespace using regexp_replace @ 2007-10-28 11:20 Thomas Kellerer <spam_eater@gmx.net> 2007-10-28 11:42 ` Re: Removing whitespace using regexp_replace Andreas Kretschmer <akretschmer@spamfence.net> 0 siblings, 1 reply; 5+ messages in thread From: Thomas Kellerer @ 2007-10-28 11:20 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing whitespace using regexp_replace 2007-10-28 11:20 Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> @ 2007-10-28 11:42 ` Andreas Kretschmer <akretschmer@spamfence.net> 2007-10-28 12:15 ` Re: Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> 0 siblings, 1 reply; 5+ messages in thread From: Andreas Kretschmer @ 2007-10-28 11:42 UTC (permalink / raw) To: pgsql-sql Thomas Kellerer <spam_eater@gmx.net> schrieb: > 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). 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) > > I tried > > SELECT regexp_replace(myfield, '\A\s*', '') > FROM mytable; For regexp_replace() you need an extra parameter 'g' like below: test=*# select 'x' || regexp_replace(regexp_replace(' \t\tfoo bar ', '^[ \t]+','','g'),'[ \t]+$','','g') || 'x'; ^^^ ^^^ Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing whitespace using regexp_replace 2007-10-28 11:20 Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> 2007-10-28 11:42 ` Re: Removing whitespace using regexp_replace Andreas Kretschmer <akretschmer@spamfence.net> @ 2007-10-28 12:15 ` Thomas Kellerer <spam_eater@gmx.net> 2007-10-28 12:32 ` Re: Removing whitespace using regexp_replace Andreas Kretschmer <akretschmer@spamfence.net> 0 siblings, 1 reply; 5+ messages in thread From: Thomas Kellerer @ 2007-10-28 12:15 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing whitespace using regexp_replace 2007-10-28 11:20 Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> 2007-10-28 11:42 ` Re: Removing whitespace using regexp_replace Andreas Kretschmer <akretschmer@spamfence.net> 2007-10-28 12:15 ` Re: Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> @ 2007-10-28 12:32 ` Andreas Kretschmer <akretschmer@spamfence.net> 2007-10-28 14:35 ` Re: Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> 0 siblings, 1 reply; 5+ messages in thread From: Andreas Kretschmer @ 2007-10-28 12:32 UTC (permalink / raw) To: pgsql-sql Thomas Kellerer <spam_eater@gmx.net> schrieb: > 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? Oh, yes. > > > >For regexp_replace() you need an extra parameter 'g' like below: > Cool, works like a charm. Nice, i'm glad to help you. > But it seems my problem was actually caused by something else: > > SELECT regexp_replace(myfield, '\s*', '', 'g') > FROM mytable; you should escape the \, change to ...'\\s*'... But without anchors this replaces all whitespaces, also within the text and not only at the beginning/end (^ and $) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing whitespace using regexp_replace 2007-10-28 11:20 Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> 2007-10-28 11:42 ` Re: Removing whitespace using regexp_replace Andreas Kretschmer <akretschmer@spamfence.net> 2007-10-28 12:15 ` Re: Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> 2007-10-28 12:32 ` Re: Removing whitespace using regexp_replace Andreas Kretschmer <akretschmer@spamfence.net> @ 2007-10-28 14:35 ` Thomas Kellerer <spam_eater@gmx.net> 0 siblings, 0 replies; 5+ messages in thread From: Thomas Kellerer @ 2007-10-28 14:35 UTC (permalink / raw) To: pgsql-sql Andreas Kretschmer wrote on 28.10.2007 13:32: >> But it seems my problem was actually caused by something else: >> >> SELECT regexp_replace(myfield, '\s*', '', 'g') >> FROM mytable; > > you should escape the \, change to ...'\\s*'... Ah! Didn't think this was necessary, as \t or \n did not need to be escaped. > But without anchors this replaces all whitespaces, also within the text > and not only at the beginning/end (^ and $) Yes of course, this was only for testing ;) Thanks for your help! Thomas ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-10-28 14:35 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-10-28 11:20 Removing whitespace using regexp_replace Thomas Kellerer <spam_eater@gmx.net> 2007-10-28 11:42 ` Andreas Kretschmer <akretschmer@spamfence.net> 2007-10-28 12:15 ` Thomas Kellerer <spam_eater@gmx.net> 2007-10-28 12:32 ` Andreas Kretschmer <akretschmer@spamfence.net> 2007-10-28 14:35 ` Thomas Kellerer <spam_eater@gmx.net>
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