Thread: [Urgent] Regexp_replace question
Hi, How can I remove characters that form a part of regular expressions? I would like to remove all instances of the following characters: [ ] \ + Given that these all mean something in regexp, I am trying to prefix them with a backslash, but it doesn't work. I tried the following: update TABLE set COLUMN = regexp_replace(COLUMN, E'[\\\[\]\+]+', '') ; This did not work so I tried the naked characters separated by a pipe, as follows: update TABLE set COLUMN= regexp_replace(COLUMN, E'[\|[|]|+]+', '') ; But this of course did not work either. I've read and re-read the pattern matching doc page ( http://snipurl.com/pg_regexp ) but it doesn't seem to offer much help. Any thoughts on what I am doing wrong?
On Tue, Sep 25, 2007 at 01:36:26PM +0800, Phoenix Kiula wrote: > How can I remove characters that form a part of regular expressions? Why do you want to do that? > I would like to remove all instances of the following characters: > > [ > ] > \ > + test=> select id, t, regexp_replace(t, e'[\\\\[\\]+]', '', 'g') from foo; id | t | regexp_replace ----+------------+---------------- 1 | foo[]+\bar | foobar (1 row) test=> select id, t, translate(t, e'[]\\+', '') from foo; id | t | translate ----+------------+----------- 1 | foo[]+\bar | foobar (1 row) -- Michael Fuhr
On 25/09/2007, Michael Fuhr <mike@fuhr.org> wrote: > > How can I remove characters that form a part of regular expressions? > > Why do you want to do that? Because these values were inserted into the DB due to a faulty application. So cleansing was called for. I just ended up doing it with replace instead of regexp_replace, one character at a time. Thanks!