DB Replacer

Append Database Entry

9 months 3 weeks ago #78169

Greg Thorsell's Avatar Greg Thorsell

I have a lot of cities and states in my database and need to change the value of all the cities in my database.

If I want to change the value of all the cities in the state of Massachusetts, I set the where statement to control_value = '*MA*' and I am presented with a list of all the cities within the state of Massachusetts.

Can you tell me how I can add -MA to the end of every city name in the field labeled value?

So for instance, I see a city with a value of Boston and I need to change it to Boston-MA. I need to do this for every city in the state of Massachusetts.

9 months 3 weeks ago #78173

Peter van Westen's Avatar Peter van Westen Admin

In the search use Regular Expressions:

(.*?)
In the replacement, do:
\1-MA

9 months 2 weeks ago #78235

Greg Thorsell's Avatar Greg Thorsell

I followed the instructions above and I'm not sure I am doing this right.

I expected the field I am modifying to display the current city name (Avon) with a -MA after it (Avon-MA).

Instead, here is what I am seeing in the city field when I use the code above for a city named Avon.

-MAAA-MA-MAvv-MA-MAoo-MA-MAnn-MA-MA

It looks like it's adding a -MA to each letter in the city name.

9 months 2 weeks ago #78236

Peter van Westen's Avatar Peter van Westen Admin

Sorry, search should be:

(.*)

9 months 2 weeks ago #78238

Greg Thorsell's Avatar Greg Thorsell

Getting closer.

The replace now reads as follows:

Avon-MA -MA

It looks to add the -MA twice

9 months 2 weeks ago #78242

Peter van Westen's Avatar Peter van Westen Admin

So those are fields where you have already appended the state?
You can do this to find all cities that do not contain a '-':

^([^-]+)$

9 months 2 weeks ago #78260

Greg Thorsell's Avatar Greg Thorsell

Hi Peter,

Thanks for the update.

So, I haven't actually done any replacements yet. I've attached an image of what I am seeing once I input the code you have provided.

Confidential information:
(hidden)


As you can see, the -MA is showing twice.

9 months 2 weeks ago #78263

Peter van Westen's Avatar Peter van Westen Admin

Change your search to:

^(.*)$

9 months 2 weeks ago #78269

Greg Thorsell's Avatar Greg Thorsell

Cool, that worked...Thank You.

I have one other scenario I need to resolve.

The listings that use these cities now need to be updated to match the new city values I just updated. For listings, the city value is stored with asterisks around the city name. For example listing within the city of Avon, has a city value of *Avon*. Is there an easy way to add the -MA before the last asterisk so it matches the city values I updated in the other table?

So I need to change *Avon* to *Avon-MA*

Thanks again for the fantastic support!

9 months 2 weeks ago #78270

Peter van Westen's Avatar Peter van Westen Admin

^(.*)\*$
\1-MA*

9 months 2 weeks ago #78275

Greg Thorsell's Avatar Greg Thorsell

Wow, you da man.

Thanks for such great support!