Re: regexp_replace

[John McKown <john.archie.mckown@xxxxxxxxx>]

How about:

select regexp_replace('71.09.6.01.3', '(\d)[.-]', '\1', 'g');

?

In your example, the (\d)[.-](\d) says find a digit followed by a period or dash followed by another digit. The first time through 1.0 is matched and replaced with 10 (710) with the "current location" pointing before the 9. Go again and 9.6 is replaced by 96 for (71096) with the "current location" pointing to the period! So ".0" doesn't match. (71096.0) next match is 1.3 and result is 13 ( 71096.013). If you don't want to eliminate the period or dash unless it is _between_ two digits, try:

select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1', 'g');

(?=\d) is a "look ahead") match which says that the period or dash must be followed by a digit, but the _expression_ _does not_ "consume" the digit matched.

On Thu, Jan 14, 2016 at 1:43 PM, Andy Colson <andy@xxxxxxxxxxxxxxx> wrote:

Hi all.

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)

It acts the same with dashes:
select regexp_replace('71-09-6-01-3', '(\d)[.-](\d)', '\1\2', 'g');

 regexp_replace
----------------
 71096-013
(1 row)

I cannot use translate because there is other text in the field.  I'm trying to strip masking characters from a parcel number in a larger text field (for example:  "the parcel 12-34-56 has caught on fire")

I seem to be missing something, any hints?

I'm on PG 9.3.9 on Slackware64.

Thanks for your time,

-Andy


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

Werner Heisenberg is driving down the autobahn. A police officer pulls
him over. The officer says, "Excuse me, sir, do you know how fast you
were going?"
"No," replies Dr. Heisenberg, "but I know where I am."

Computer Science is the only discipline in which we view adding a new wing to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

He's about as useful as a wax frying pan.

Maranatha! <><
John McKown