Moving dynamic text from a text string in MySQL to another place in the string

Question

I have several thousand MySQL records containing lines of text stored in a MySQL database that contain unique values ​​after a URL variable called "add_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=

This dynamic value is always in the variable "add_development =". If it doesn't matter here, it just looks likeadditional_development=&additional_postal_code=

I need to remove dynamic text from this location into a variable ?fullinfo=without knowing the contents (Chestnut Estates) in order to navigate, only the location where this value is 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 that I could not do this with a regular expression. Therefore, I tried to execute commands with different degrees of update, which still do not affect.

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

The table name is wp_posts and the column name is post_content.

Answer 1

Summary

The SQL below uses the MySQL INSERTstring function to replace part of a string. This can be done in one query, and not in two if necessary, but SQL will 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 */
         '');

Answer 2

, WHERE < . SELECT, .

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),
        ''
    )
);

,

Answer 3

Is the additional_developmentonly dynamic part? If so, just replace the line:

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

Otherwise, it seems that you will have to implement this in a different way, either by implementing the regular expression, replace yourself in MySQL (there are many examples), or do it in your choice of programming language by choosing the regular expression in the update language. It's slower to do it, of course, but you don't have much choice (other than switching databases).

Unfortunately