find and replace repeating string in database

Question

I've problematic data in some row of my talbe like following string

[data attr1='2300.48' attr1='1' attr1='1116.95' attr1='60.75' attr1='9.22' attr1='' attr1='0' attr1='3593.39' attr1='119.05' attr1='25.96' attr1='27.98' attr1='36.32']

[data attr1='2300.48' attr1='1' attr1='1116.95' attr1='60.75' attr1='9.22' attr1='' attr1='0' attr1='3593.39' attr1='119.05' attr1='25.96' attr1='27.98' attr1='36.32']

[data attr1='2300.48' attr1='1' attr1='1116.95' attr1='60.75' attr1='9.22' attr1='' attr1='0' attr1='3593.39' attr1='119.05' attr1='25.96' attr1='27.98' attr1='36.32']

[data attr1='2300.48' attr1='1' attr1='1116.95' attr1='60.75' attr1='9.22' attr1='' attr1='0' attr1='3593.39' attr1='119.05' attr1='25.96' attr1='27.98' attr1='36.32']

Now i want to replace them by only one same string like:

[data attr1='2300.48' attr1='1' attr1='1116.95' attr1='60.75' attr1='9.22' attr1='' attr1='0' attr1='3593.39' attr1='119.05' attr1='25.96' attr1='27.98' attr1='36.32']

Please keep in mind that the value of the attr is varying.

-- update --

how its stored in database the column name is called content and the value in this column is

the text of the article
 [data.... ]
 [data.... ]
 [data.... ]

the datatype of the content is longtext

Sorry i can't make the proper structure of sql table.

Thanks in advance for your help.

how is they stored in the database? just a text field?

Puggan Se
– Puggan Se

2012-07-31 11:35:15 +00:00
Commented Jul 31, 2012 at 11:35 — Puggan Se
– Puggan Se, Commented Jul 31, 2012 at 11:35
Check this out : stackoverflow.com/questions/1651999/…

mlishn
– mlishn

2012-07-31 11:36:49 +00:00
Commented Jul 31, 2012 at 11:36 — mlishn
– mlishn, Commented Jul 31, 2012 at 11:36
plz provide your table structure

Joe G Joseph
– Joe G Joseph

2012-07-31 11:37:27 +00:00
Commented Jul 31, 2012 at 11:37 — Joe G Joseph
– Joe G Joseph, Commented Jul 31, 2012 at 11:37
is all these duplicate strings are separated by "\n" ?

metalfight - user868766
– metalfight - user868766

2012-07-31 11:40:28 +00:00
Commented Jul 31, 2012 at 11:40 — metalfight - user868766
– metalfight - user868766, Commented Jul 31, 2012 at 11:40
@user868766 No there is no "\n"

Gautam Arya
– Gautam Arya

2012-07-31 11:51:26 +00:00
Commented Jul 31, 2012 at 11:51 — Gautam Arya
– Gautam Arya, Commented Jul 31, 2012 at 11:51

Joni · Accepted Answer · 2012-07-31 11:36:37Z

2

You can use DISTINCT or GROUP BY to remove duplicate values, depending on other needs. For example:

select distinct mycolumn from mytable;

Or:

select mycolumn from mytable group by mycolumn;

answered Jul 31, 2012 at 11:36

Joni

112k14 gold badges151 silver badges201 bronze badges

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

Comments

Puggan Se · Accepted Answer · 2012-07-31 13:11:15Z

0

If you want to remove/delete duplicate rows you can use

DELETE t 
FROM t 
LEFT JOIN t AS t2 ON (t.content = t2.content AND t.id > t2.id) 
WHERE t2.id;

Before:

SELECT * FROM t;
+----+---------+
| id | content |
+----+---------+
|  1 | abc     |
|  5 | abc123  |
|  9 | abc123  |
| 13 | abc     |
| 17 | abc     |
+----+---------+

After:

SELECT * FROM t;
+----+---------+
| id | content |
+----+---------+
|  1 | abc     |
|  5 | abc123  |
+----+---------+

answered Jul 31, 2012 at 13:11

Puggan Se

5,8462 gold badges25 silver badges51 bronze badges

Comments

Gautam Arya · Accepted Answer · 2012-10-02 07:08:55Z

Here is the script that made my work.

$link = mysqli_connect("SERVER", "root", "root");
    $db = mysqli_select_db($link, "test");
    $res = mysqli_query($link, "SELECT * FROM wp_posts WHERE post_content LIKE '%nutr-label servingsize%nutr-label servingsize%'");

    if($res){
        $i=1;
        while($row = mysqli_fetch_array($res)){
            $str1 = strrpos($row['post_content'],"[nutr-label servingsize='");
            $sub1 = substr($row['post_content'],$str1);
            $str2 = strrpos($row['post_content'],"']");

            $finalStr = substr($row['post_content'],$str1,$str2);
            $newPostContent = substr($row['post_content'], 0, strpos($row['post_content'],"[nutr-label servingsize='")).$finalStr;
            $up = mysqli_query($link,"UPDATE wp_posts SET post_content ='".addslashes($newPostContent)."' WHERE ID='".$row['ID']."'");
            $i++;
        }
    }else{
        echo mysqli_error();
    }

Collectives™ on Stack Overflow

find and replace repeating string in database

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related