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, Jun 12 '17 at 08:36
It's always in the same order but the values are different for different subdivisions. — Rocco The Taco, Jun 12 '17 at 11:56

score 3 · Answer 1 · edited Jun 12 '17 at 05:51

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 '17 at 05:51

cn007b

16,596
7
59
74

answered Jun 11 '17 at 01:29

Mihai

26,325
7
66
81

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 – Thomas G Jun 12 '17 at 08:38
@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: http://rextester.com/WPFZ46684 – Rocco The Taco Jun 13 '17 at 18:50

Steve Chambers · Accepted Answer · 2017-06-13T15:14:03.340

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 */
         '');

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? — Rocco The Taco, Jun 13 '17 at 13:54
Sure - have just updated my answer accordingly (and fixed the demo link). — Steve Chambers, Jun 13 '17 at 15:15
Close but it's still missing this portion? How does the updated answer match these aspects: — Rocco The Taco, Jun 13 '17 at 17:33
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? — Steve Chambers, Jun 13 '17 at 19:26

score 0 · Answer 3 · answered Jun 08 '17 at 17:46

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 08 '17 at 17:46

Conor Mancone

1,940
16
22

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? – Rocco The Taco Jun 08 '17 at 18:22
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! – Conor Mancone Jun 08 '17 at 18:41

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

3 Answers3