Re: regexp_replace

On 1/14/2016 1:59 PM, Tom Lane wrote:
> Andy Colson <andy(at)squeakycode(dot)net> writes:
>> This is not doing as I'd expected:
>
>> select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g');
>
>> regexp_replace
>> ----------------
>> 71096.013
>> (1 row)
>
> I think regexp_replace considers only non-overlapping substrings,
> eg, once it's replaced 1.0 with 10, it then picks up searching at
> the 9 rather than starting over. The dot after 6 doesn't get
> removed because the 6 can't belong to two replaceable substrings, and
> it already got consumed in the process of removing the dot before 6.
>
> I might be wrong, but I think two passes of regexp_replace would
> do what you want in this example.
>
> regards, tom lane
>

Ah, that would make sense, and seems to explain:

select regexp_replace('7-9-6-1-3', '(\d)[.-](\d)', '\1\2', 'g');

regexp_replace
----------------
79-61-3
(1 row)

select regexp_replace('71-09-56-01-53', '(\d)[.-](\d)', '\1\2', 'g');

regexp_replace
----------------
7109560153
(1 row)

I can work two passes in. Thanks Tom!

-Andy