Is there a regexp_replace equivalent for postgresql 7.4?

Question

I'm trying to use SELECT regexp_replace(m.*, '[\n\r]+', ' ', 'g') to remove carriage returns and new lines from my field to generate a CSV from my table; however, looks like my postgresql version (7.4.27) does not support that function.

function regexp_replace(members, "unknown", "unknown", "unknown") does not exist

I also tried doing it this way:

SELECT replace(replace(m.*, '\r', ''), '\n', '')

function replace(members, "unknown", "unknown") does not exist

No function matches the given name and argument types. You may need to add explicit type casts.

or this way:

SELECT replace(replace(m.*, chr(13), ''), chr(10), '')

function replace(members, text, "unknown") does not exist

and still got similar errors.

How can a achieve that using another function or solution?

It's not under my control to upgrade this database unfortunately. — farjam, Dec 04 '15 at 17:54

Erwin Brandstetter · Accepted Answer · 2015-12-05T14:55:36.523

m.* makes no sense where you put it. It would work like this:

SELECT replace(replace(m.some_column, chr(13), ''), chr(10), '')
FROM   tbl m;

But that just removes all "linefeed" and "carriage return" characters completely instead of replacing each string consisting only of these characters with a single space character like your original. If that's what you want, single character replacement is simpler and cheaper with translate() - also available in ancient pg 7.4:

SELECT translate(some_column, chr(13) || chr(10), '');

To achieve what your original regexp_replace() does (just without the nonsensical m.*), identify a single character that's not in the string and use that as stepping stone. Say: ° does not pop up, then:

SELECT replace(replace(replace(
        translate(some_column, chr(13) || chr(10), '°')  -- replace with dummy
      , '°°', '°')  -- consolidate to single dummy
      , '°°', '°')  -- repeat as many times as necessary
      , '°', ' ');  -- replace dummy with space

Looks awkward, and it's imperfect: fails for too many consecutive line breaks. But it's probably still faster than regexp_replace(), even in modern Postgres, because regular expressions are much more expensive. Then again, performance is probably not an issue here.

Upgrade to modern Postgres and you don't need this.

Is there a regexp_replace equivalent for postgresql 7.4?

1 Answers1

Linked