Postgres: regexp_replace & trim

Question

I need to remove '.0' at the end of the string but I have some issues.

In PG 8.4 I have this expression and its was worked fine.

select regexp_replace('10.1.2.3.0', '(\\\\.0)+$', '');

and result was

'10.1.2.3' - good result.

But after PG was updated to 9.x version result is

'10.1.2.3.0' - the input string and its not ok.

Also I tried to use trim function

it this case it is ok

select trim('.0' from '10.1.2.3.0');

result is '10.1.2.3' - ok

but when I have 10 at the end of the code I have unexpected result

select trim('.0' from '10.1.2.3.10.0');

or

select trim('.0' from '10.1.2.3.10');

result is 10.1.2.3.1 - 0 is trimmed from 10

Somebody can suggest me solution and explain what is wrong with trim function and what was changed in regexp_replace in latest versions?

Not seen anything stating why this does what it does - trim removes the longest string containing ANY of the matching characters from the start and end of the string, so '.0' removes any '0' or '.' characters at the end of the string, so from '10.0' it removes both zeros and the period, leaving just '1' behind. — samjudson
– samjudson, Commented Jun 20 at 12:18

Gordon Linoff · Accepted Answer · 2016-09-10 17:25:57Z

3

I would suggest doing something like this:

select (case when col like '%.0' then left(col, length(col) - 2)
             else col
        end)

This will work in all versions of Postgres and you don't need to worry about regular expression parsing.

As for the regular expression version, both of these work for me (on recent versions of Postgres):

select regexp_replace('10.1.2.3.0', '(\.0)+$', '');
select regexp_replace('10.1.2.3.0', '([.]0)+$', '');

I suspect the problem with the earlier version is the string parsing with the backslash escape character -- you can use square brackets instead of backslash and the pattern should work in any version.

edited Sep 10, 2016 at 17:25

answered Sep 10, 2016 at 12:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dumitru Over a year ago

Thank you! :) Yes problem is with backslash escape character and its behavior was changed across versions.

Collectives™ on Stack Overflow

Postgres: regexp_replace & trim

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related