Jump to content

Find and replace string in field?


Mutley

Recommended Posts

I've just updated my forum software to IPBv4 and it's inserted a paragraph line in the signatures field, so they are really spaced out, I want to search and remove this line from the field. In some instances, there is more than one occurance in the field value.

 

 

This is the string I want to remove:

<p style="text-align:center;"> </p>

An example of one members signature in the database:

<p style="text-align:center;"><img src="http://i1382.photobucket.com/albums/ah259/maphilli/Axial%20Yeti/Logos/MS%20Banner_zpsfrtm0dpx.jpg" alt="MS%20Banner_zpsfrtm0dpx.jpg"></p>
<p style="text-align:center;"> </p>
<p style="text-align:center;"><a href="<___base_url___>/topic/199518-zombis-axial-yeti/">My Axial Yeti Build Thread</a></p>
<p style="text-align:center;"> </p>
<p style="text-align:center;"><a href="<___base_url___>/topic/202762-zombi/">My Sales Feedback Page</a></p>
<p style="text-align:center;"> </p>
<p style="text-align:center;"><a href="<___base_url___>/forum/167-gowtt/">GOWTT Trailing Group Forum - Derbyshire Peak District</a></p>
<p style="text-align:center;"> </p>
<p style="text-align:center;"><a href="https://www.facebook.com/pages/Loaded-Ox/437880833003825" rel="external nofollow">Loaded Ox Facebook Page</a></p>

I want it to remove the line mentioned, so it looks like this:

<p style="text-align:center;"><img src="http://i1382.photobucket.com/albums/ah259/maphilli/Axial%20Yeti/Logos/MS%20Banner_zpsfrtm0dpx.jpg" alt="MS%20Banner_zpsfrtm0dpx.jpg"></p>
<p style="text-align:center;"><a href="<___base_url___>/topic/199518-zombis-axial-yeti/">My Axial Yeti Build Thread</a></p>
<p style="text-align:center;"><a href="<___base_url___>/topic/202762-zombi/">My Sales Feedback Page</a></p>
<p style="text-align:center;"><a href="<___base_url___>/forum/167-gowtt/">GOWTT Trailing Group Forum - Derbyshire Peak District</a></p>
<p style="text-align:center;"><a href="https://www.facebook.com/pages/Loaded-Ox/437880833003825" rel="external nofollow">Loaded Ox Facebook Page</a></p>

The table is `ipb_core_members`, column `signature`
 

What SQL query can I run? Thanks in advance.

Edited by Mutley
Link to comment
Share on other sites

Something like this

 

UPDATE `ipb_core_members` SET `signature` = replace(signature, "<p style=\"text-align:center;\"> </p>", '')

 

Thanks a lot QuickOldCar

 

I did a test on 1 member and it ran successfully but effected no rows, I'm guessing that it's not matching with that string correctly but I am not sure why? The backslash to make it parse won't cause any issues would it?

Link to comment
Share on other sites

I just use PHPMyAdmin.

 

The backslashes don't seem to cause any problems; at least they worked when I tested the query through PHPMyAdmin. Note that the query could also be written as

UPDATE `ipb_core_members` SET `signature` = replace(signature, '<p style="text-align:center;"> </p>', '')

Are the empty paragraph tags written exactly like

<p style="text-align:center;"> </p>

If there is more than one space between the open and close tag, for example, the query won't replace anything.

Link to comment
Share on other sites

The backslashes don't seem to cause any problems; at least they worked when I tested the query through PHPMyAdmin. Note that the query could also be written as

UPDATE `ipb_core_members` SET `signature` = replace(signature, '<p style="text-align:center;"> </p>', '')

Are the empty paragraph tags written exactly like

<p style="text-align:center;"> </p>

If there is more than one space between the open and close tag, for example, the query won't replace anything.

 

I just can't get it to work, yes the string is exactly that.

 

Even if I just test it on the example in my first post, it doesn't find this string. I really can't understand why. If I do a search for this string, then it finds the fields no problem, it just won't initiate this replace query.

Link to comment
Share on other sites

Did a test myself in phpmyadmin and seemed to work fine.

 

1 row(s) affected. ( Query took 0.0009 sec )

UPDATE `ipb_core_members` SET `signature` = replace( signature, "<p style=\"text-align:center;\"> </p>", '' )

 

New data:

<p style="text-align:center;"><img src="http://i1382.photobucket.com/albums/ah259/maphilli/Axial%20Yeti/Logos/MS%20Banner_zpsfrtm0dpx.jpg" alt="MS%20Banner_zpsfrtm0dpx.jpg"></p>

<p style="text-align:center;"><a href="<___base_url___>/topic/199518-zombis-axial-yeti/">My Axial Yeti Build Thread</a></p>

<p style="text-align:center;"><a href="<___base_url___>/topic/202762-zombi/">My Sales Feedback Page</a></p>

<p style="text-align:center;"><a href="<___base_url___>/forum/167-gowtt/">GOWTT Trailing Group Forum - Derbyshire Peak District</a></p>

<p style="text-align:center;"><a href="https://www.facebook.com/pages/Loaded-Ox/437880833003825" rel="external nofollow">Loaded Ox Facebook Page</a></p>
Link to comment
Share on other sites

Thanks a lot for your help so far, I just don't get why it won't work, it doesn't bring any errors but it doesn't change any rows. Here is a screenshot of the query executing and the signature field...

 

 

CAh7om0.jpg

 

 

Signature field...

 

3v996Bg.jpg

Edited by Mutley
Link to comment
Share on other sites

Does this version work for you - enclosing the search text in single quotes and no escape of the double quotes?

UPDATE `testsig` 
SET `signature` = replace( signature, '<p style="text-align:center;"> </p>', '' ) ;
mysql> UPDATE `testsig`
    -> SET `signature` = replace( signature, '<p style="text-align:center;"> </p>', '' ) ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Link to comment
Share on other sites

Same problem, this is really leaving me confused,

 

It executes fine but effects no rows. Searching for the string in fields with %LIKE%, finds all the fields with no issues too. So it can see the fields using this string.

 

What could it be? :( I really appreciate your help so far guys!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.