Move dynamic text from text string in MySQL to another location in the string

Question

I have a few thousand MySQL records that contain strings of text saved in a MySQL database that contain unique values after a URL variable called "additional_development" with data like 'Chestnut%20Estates' in each record:

<a class="button" href="http://www.websitedomain.com/search-results/?fullinfo=&Location=all&additional_development=Chestnut%20Estates&additional_postal_code=

They dynamic value is always in the &additional_development= variable. If there is no value here it simply looks like this additional_development=&additional_postal_code=

I need to remove the dynamic text from this location to the ?fullinfo= variable without knowing the contents (Chestnut Estates) to move, only the location of where this value resides in the URL.

Desired Effect:

<a class="button" href="http://www.websitedomain.com/?fullinfo=Chestnut%20Estates&Location=all&additional_development=&additional_postal_code=

Using this question, I found I could not do this with regex. So I've attempted different degrees of Update commands with still no effect.

Any suggestions on the best way to achieve the desired result in a MySQL query?

Table name is wp_posts and column name is post_content.

only the location of where this value resides in the URL --> Does this means that in all your URLs you ALWAYS have the same parameters in the same order? I see a way to do this with SUBSTR_INDEX but I need a confirmation first. — Thomas G
– Thomas G, Commented Jun 12, 2017 at 8:36
It's always in the same order but the values are different for different subdivisions. — Rocco The Taco
– Rocco The Taco, Commented Jun 12, 2017 at 11:56

cn0047 · Accepted Answer · 2017-06-12 05:51:40Z

3

UPDATE it in chunks by adding a WHERE id < whatever since it will be pretty slow.Use it as a SELECT before to make sure you get the desired results.

UPDATE t SET col = CONCAT(
    SUBSTRING_INDEX(col, '&Location', 1),
    SUBSTRING_INDEX(SUBSTRING_INDEX(col, '&additional_postal', 1), 'additional_development=', -1),
    REPLACE(
        SUBSTRING_INDEX(col, 'fullinfo=', -1),
        SUBSTRING_INDEX(SUBSTRING_INDEX(col, '&additional_postal', 1), 'additional_development=', -1),
        ''
    )
);

See it working

edited Jun 12, 2017 at 5:51

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

answered Jun 11, 2017 at 1:29

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas G Over a year ago

SUBSTR_INDEX was what I wanted to do, then I see that you were there first :) This is the the right pure SQL solution for sure

Rocco The Taco Over a year ago

@Mihai -This works great unless there are multiple, similar instances of the string in the same record. It removes everything except for the last instance? How do I overcome this? See example: rextester.com/WPFZ46684

Steve Chambers · Accepted Answer · 2017-06-13 15:14:03Z

2

+250

Summary

The SQL below uses MySQL's INSERT string function to replace part of a string. It could be done in one query rather than two if needed but the SQL would be less readable.

Demo

http://rextester.com/RTKA97873

SQL

UPDATE wp_posts
SET post_content = 
  INSERT(post_content, 
         /* Replace from character after ?fullinfo= */
         LOCATE('?fullinfo=', post_content) + 10,
         /* Replace up to the next & */
         LOCATE('&', post_content, LOCATE('?fullinfo=', post_content) + 10) 
         - LOCATE('?fullinfo=', post_content) - 10,
         /* Replace with value of &additional_development parameter
            (i.e. substring after the = and before the next ampersand) */
         SUBSTRING(
           post_content,
           LOCATE('&additional_development=', post_content) + 24,
           LOCATE('&', post_content, LOCATE('&additional_development=', post_content) + 24)
           - LOCATE('&additional_development=', post_content) - 24));

UPDATE wp_posts
SET post_content = 
  INSERT(post_content,
         /* Replace from character after &additional_development= */
         LOCATE('&additional_development=', post_content) + 24, 
         /* Replace up to the next & */
         LOCATE('&', post_content, LOCATE('&additional_development=', post_content) + 24)
         - LOCATE('&additional_development=', post_content) - 24,
         /* Replace with empty string */
         '');

edited Jun 13, 2017 at 15:14

answered Jun 12, 2017 at 15:46

Steve Chambers

39.8k29 gold badges178 silver badges222 bronze badges

4 Comments

Rocco The Taco Over a year ago

I think this will work but I need to include/locate more of the string as it's making changes I don't want. I've updated my original question, can you please review and update your answer with what is needed?

Steve Chambers Over a year ago

Sure - have just updated my answer accordingly (and fixed the demo link).

Rocco The Taco Over a year ago

Close but it's still missing this portion? How does the updated answer match these aspects: <a class="button" href="websitedomain.com/search-results/?fullinfo=

Steve Chambers Over a year ago

Sorry - not quite clear on what you're asking here. The only difference between the result in the demo and the result asked for in your question is the latter is missing /search-results. As your q didn't mention wanting this to be removed I'd assumed it was just a typo - did you actually want this to be removed?

Conor Mancone · Accepted Answer · 2017-06-08 17:46:17Z

0

Is additional_development the only dynamic part? If so this is a simple string replace:

UPDATE table_name SET column_name=replace( column_name, '?fullinfo=&Location=all&additional_development=', '?fullinfo=' )

Otherwise it looks like you will have to implement this in another way, either by implementing a regular expression replace yourself in MySQL (there are plenty of examples around), or doing it in your programming language of choice via select, regexp in language, update. It is slower to do it that way of course, but you don't have much choice (other than switching databases).

Sorry

answered Jun 8, 2017 at 17:46

Conor Mancone

2,03017 silver badges23 bronze badges

2 Comments

Rocco The Taco Over a year ago

Yes, but it's the neighborhood name value after &additional_development= and before the &Location= that is dynamic. I need to move that to after ?fullinfo= I don't see how your UPDATE actually moves the data found in those locations to the new location?

Conor Mancone Over a year ago

The query I have wipes out whatever is in the location= query parameter. From reading your question it wasn't clear to me that that part was supposed to move as well. I thought you were just trying to move additional_development to fullinfo, which my simple replace does do (by destroying the location part). sorry it doesn't help!

Collectives™ on Stack Overflow

Move dynamic text from text string in MySQL to another location in the string

3 Answers 3

2 Comments

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related