Gotcha in replace

[Tom Jenkins]

Hi Tobias,

Tobias Klausmann wrote:

> Tom Jenkins <tjenkins at devis.com> wrote:
> 
>>Hello all,
>>While I was working on some database scripting, I got bit by this 
>>behavior in replace.
>>
>>
>>>>>y = "insert into blah values ('hello','','','','bye')"
>>>>>y.replace(",'',", ",' ',")
>>>>>
>>"insert into blah values ('hello',' ','',' ','bye')"
>>
>>Notice that the second matching set of ",''," characters are not 
>>replaced probably because the first and second matching sets overlap on 
>>the last and first character, respectively.
>>
>>It was trivial for this script to work around the gotcha (I just put the 
>>replace in a loop and broke out when new string equalled the old 
>>string).  But that method may not be an appropriate workaround in all cases.
>>
>>Is this known behavior and I just missed it; or is this a bug in replace?
>>
> 
> I wouldn't call it a bug even if it's a gotcha. You're
> replacing ,'', with ,' ', and then look for the next
> occurence of ,'',. The search starts *after* what you
> just replaced, therefore it doesn't match (there would
> have to be something like ,'',,'', ).


Sure, that's what I meant by the last and first characters overlapping 
(obviously it was late and I didn't make myself clear in the post - 
sorry).  I was just wondering if that behavior - restarting the search 
*after* the replacement was the intended behavior.  I can live with it 
either way, really.


> 
> Maybe a better replace-call would be:
> y.replace(",''", ",' '")
> By this, the next occurence should be found correctly.
> 


Yes that would be a better replace-call for the dummy string I posted. 
Unfortunately, the actual data contains numerous comma-quote-quote (,'') 
entries like so: "...,'','', '.... the following 
''Quote1'',''Quote2'',and ''Quote3''.....',...."

The database dump that generates the file I'm using automatically 
escapes single quotes with another quote.

Maybe I'll just go to the person who created the destination database 
and have them change those fields marked NOT NULL, then I wouldn't have 
to do any replace calls <wink>.

yeah-why-am-i-working-around-the-dba's-mistake-ly yours,
Tom
-- 
Tom Jenkins
devIS - Development Infostructure
http://www.devis.com