regexp_replace in two times?
Hi all,
I used the regexp_replace function to make a substitution over a table, but I
got a strange behaviour (please consider I'm not an expert of regex). The
idea is to remove the final part of a code, that could be TIF, ISTTIF, tif,
isttif, and at the same time consider only the records depending on the join
with another table. Now, the strange thing is that the first query updated
the most of records, but then 4 records are still there, and in fact
executing again the same update provides me another substitution. What could
be the reason?
db=> begin;
BEGIN
raydb=> update elementi_dettagliset codice = regexp_replace(
upper(codice), '(IST)*TIF$', '')
where id_elemento in (
select ed.id_elemento
from elementi_dettagli ed, elementi e
where ed.id_elemento = e.id_elemento
and e.categoria = 'bozzetti'
and ed.codice ~* '(IST)*TIF$'
);
UPDATE 4679
db=> select ed.id_elemento,ed.codice from elementi_dettagli ed, elementi e
where ed.codice like '%TIF' and ed.id_elemento = e.id_elemento and
e.categoria='bozzetti';
id_elemento | codice
-------------+--------------
68904 | 0M0809532TIF
67732 | Y07236TIF
67608 | 0D0731744TIF
65687 | 0M0708711TIF
(4 rows)
db=> update elementi_dettagliset codice = regexp_replace(
upper(codice), '(IST)*TIF$', '')
where id_elemento in (
select ed.id_elemento
from elementi_dettagli ed, elementi e
where ed.id_elemento = e.id_elemento
and e.categoria = 'bozzetti'
and ed.codice ~* '(IST)*TIF$'
);
UPDATE 4
db=> select version();
version
----------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3
20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)
(1 row)
Thanks,
Luca
Luca Ferrari <fluca1978@infinito.it> writes:
I used the regexp_replace function to make a substitution over a table, but I
got a strange behaviour (please consider I'm not an expert of regex). The
idea is to remove the final part of a code, that could be TIF, ISTTIF, tif,
isttif, and at the same time consider only the records depending on the join
with another table. Now, the strange thing is that the first query updated
the most of records, but then 4 records are still there, and in fact
executing again the same update provides me another substitution. What could
be the reason?
Maybe the original strings had more than one instance of 'TIF'?
regards, tom lane
On Thursday 8 May 2008 Tom Lane's cat, walking on the keyboard, wrote:
Maybe the original strings had more than one instance of 'TIF'?
Ops....you're right, I've checked with a backup copy and I found four records
with the double tif pattern.
I should have get it before....sorry!
Luca