Operation Search and Replace problem

[Peter James]

I am tidying up a table where people have been a bit slack. For example in a firstname field quite a few entries many be Bill&Joan and I want to replace those records with Bill & Joan. I have tried the operation and the records remain the same. I even tried padding with trailing blanks to fill the field but with no success.
What am I doing wrong?

[MoGrace]

Re: Operation Search and Replace problem

I am tidying up a table where people have been a bit slack. For example in a firstname field quite a few entries many be Bill&Joan and I want to replace those records with Bill & Joan. I have tried the operation and the records remain the same. I even tried padding with trailing blanks to fill the field but with no success.
What am I doing wrong?

If it is only the firstname field that needs updating you could try adding a field to the table and then update that field with the string you want from the other field. When it looks like you want, you can update the original field with the new field's data then drop the new field.

If there are several fields that need to be changed, you might try creating a temp table of just those fields and a unique id field to link the records. Change the temp table then append it to overwrite the old data. By using a temp table you can delete the records that don't need changing before the append.

And of course back up the table first!

Another thought. I had to do this on an inventory table that had the description field all messed up. I did several searches on the table first to see how many records and which errors I needed to correct. Then I did my updates in stages to address each sort of error until I got them all. Marking and unmarking the records helps here.

[Peter James]

Re: Operation Search and Replace problem

Thanks Robin
I tried the update but still come down to the search and replace problem. Even using the expression option trimming the field in the condition column had no effect.
However using search and replace "&" with " & " proved effective. I can live with the extra blanks where the & already had one on each side. It sort of begs the question why it would not work with the text on either side.

Peter

[Stan Mathews]

Re: Operation Search and Replace problem

It seems to me that the operation genie creates incorrect code (behind the operation).

Try this script using your real table and field names.

a_tbl = table.open("names")

update.fields = 4
update.field1 = "Name"
update.expr1 = "STRITRAN(NAME,\" & \",\"&\")"
update.field2 = "NAME"
update.expr2 = "STRITRAN(NAME,\" &\",\"&\")"
update.field3 = "NAME"
update.expr3 = "STRITRAN(NAME,\" &\",\"&\")"
update.field4 = "NAME"
update.expr4 = "STRITRAN(NAME,\"&\",\" & \")"

a_tbl.update()

It is designed to remove all single spaces before and after the & whether it be

" &"
" & "
"& "

and then put one on both sides of the &.

[MoGrace]

Re: Operation Search and Replace problem

That is too cool Stan. I never would have thought of running the update on the same field more than once in the same operation!

[Stan Mathews]

Re: Operation Search and Replace problem

That is too cool Stan. I never would have thought of running the update on the same field more than once in the same operation!

Thanks.

It isn't exactly intuitive, is it?

There is probably a regex expression which simplify the whole into one replacement but I'm still learning that aspect.

[Peter James]

Re: Operation Search and Replace problem

Thanks Stan
I have shuffled your code off to CODE for immediate use and for future use if needed.
Cheers
Peter

[MikeC]

Re: Operation Search and Replace problem

There is probably a regex expression which simplify the whole into one replacement

Maybe this...

vDataLine="Bill                      &         Judy"
?regex_merge(vDataLine,"\s+&+\s+"," & ")
= "Bill & Judy"

vDataLine="Bill                      &Judy"
?regex_merge(vDataLine,"\s+&+\s+"," & ")
= "Bill & Judy"

vDataLine="Bill&Judy"
?regex_merge(vDataLine,"\s+&+\s+"," & ")
= "Bill & Judy"

Here's another in case users may have doubled up on the "&"

vDataLine="Bill      & &      Judy"
?regex_merge(vDataLine,"\s+&+\s+&+\s+"," & ")
= "Bill & Judy"

So suppose in this case it might be ??

regex_merge(name,"\s+&+\s+"," & ")

.