Re: regexp_replace

On 1/14/2016 1:59 PM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.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