sql query to put quotes around numbers in img tag

Question

You are simply going to love this one

First of all, here is a sample table with data loaded:

mysql> use junk
Database changed
mysql> drop table todd;
Query OK, 0 rows affected (0.01 sec)

mysql> create table todd (id int not null auto_increment,url VARCHAR(255),
    -> primary key (id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO todd (url) VALUES
    -> ('<img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496>'),
    -> ('<img height=329 alt="" src="http://www.example.com/images/myexample.jpg" width=130>'),
    -> ('<img height=339 alt="" src="http://www.example.com/images/myexample.jpg" width=206>'),
    -> ('<img height=349 alt="" src="http://www.example.com/images/myexample.jpg" width=498>'),
    -> ('<img height=359 alt="" src="http://www.example.com/images/myexample.jpg" width=499>');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from todd;
+----+-------------------------------------------------------------------------------------+
| id | url                                                                                 |
+----+-------------------------------------------------------------------------------------+
|  1 | <img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496> |
|  2 | <img height=329 alt="" src="http://www.example.com/images/myexample.jpg" width=130> |
|  3 | <img height=339 alt="" src="http://www.example.com/images/myexample.jpg" width=206> |
|  4 | <img height=349 alt="" src="http://www.example.com/images/myexample.jpg" width=498> |
|  5 | <img height=359 alt="" src="http://www.example.com/images/myexample.jpg" width=499> |
+----+-------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

You must run two queries one after the other:

This one puts doublequotes around the number height

UPDATE 
(select id,CONCAT(bftoken,token,'"',num,'"',substr(aftoken,num_length+1)) newurl
FROM (select id,token,
substr(b.url,1,a.hpos - 1) bftoken,
substr(b.url,a.hpos + length(a.token)) aftoken,
substr(b.url,a.hpos + length(a.token))+0 num,
length(substr(b.url,a.hpos + length(a.token))+0) num_length
from
(select id,token,LOCATE(token,url) hpos
from todd,(select 'height=" token) w
WHERE LOCATE(CONCAT(token,""'),url)=0) A
INNER JOIN todd B USING (id)) AA) AAA
INNER JOIN todd BBB USING (id)
SET BBB.url = AAA.newurl;

This one puts doublequotes around the number width

UPDATE 
(select id,CONCAT(bftoken,token,'"',num,'"',substr(aftoken,num_length+1)) newurl
FROM (select id,token,
substr(b.url,1,a.hpos - 1) bftoken,
substr(b.url,a.hpos + length(a.token)) aftoken,
substr(b.url,a.hpos + length(a.token))+0 num,
length(substr(b.url,a.hpos + length(a.token))+0) num_length
from
(select id,token,LOCATE(token,url) hpos
from todd,(select 'width=" token) w
WHERE LOCATE(CONCAT(token,""'),url)=0) A
INNER JOIN todd B USING (id)) AA) AAA
INNER JOIN todd BBB USING (id)
SET BBB.url = AAA.newurl;

Watch what happens when I run these and show table contents:

mysql> UPDATE
    -> (select id,CONCAT(bftoken,token,'"',num,'"',substr(aftoken,num_length+1)) newurl
    -> FROM (select id,token,
    -> substr(b.url,1,a.hpos - 1) bftoken,
    -> substr(b.url,a.hpos + length(a.token)) aftoken,
    -> substr(b.url,a.hpos + length(a.token))+0 num,
    -> length(substr(b.url,a.hpos + length(a.token))+0) num_length
    -> from
    -> (select id,token,LOCATE(token,url) hpos
    -> from todd,(select 'height=" token) w
    -> WHERE LOCATE(CONCAT(token,""'),url)=0) A
    -> INNER JOIN todd B USING (id)) AA) AAA
    -> INNER JOIN todd BBB USING (id)
    -> SET BBB.url = AAA.newurl;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> UPDATE
    -> (select id,CONCAT(bftoken,token,'"',num,'"',substr(aftoken,num_length+1)) newurl
    -> FROM (select id,token,
    -> substr(b.url,1,a.hpos - 1) bftoken,
    -> substr(b.url,a.hpos + length(a.token)) aftoken,
    -> substr(b.url,a.hpos + length(a.token))+0 num,
    -> length(substr(b.url,a.hpos + length(a.token))+0) num_length
    -> from
    -> (select id,token,LOCATE(token,url) hpos
    -> from todd,(select 'width=" token) w
    -> WHERE LOCATE(CONCAT(token,""'),url)=0) A
    -> INNER JOIN todd B USING (id)) AA) AAA
    -> INNER JOIN todd BBB USING (id)
    -> SET BBB.url = AAA.newurl;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from todd;
+----+-----------------------------------------------------------------------------------------+
| id | url                                                                                     |
+----+-----------------------------------------------------------------------------------------+
|  1 | <img height="319" alt="" src="http://www.example.com/images/myexample.jpg" width="496"> |
|  2 | <img height="329" alt="" src="http://www.example.com/images/myexample.jpg" width="130"> |
|  3 | <img height="339" alt="" src="http://www.example.com/images/myexample.jpg" width="206"> |
|  4 | <img height="349" alt="" src="http://www.example.com/images/myexample.jpg" width="498"> |
|  5 | <img height="359" alt="" src="http://www.example.com/images/myexample.jpg" width="499"> |
+----+-----------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

mysql> select * from todd;

Give it a Try !!!

CAVEAT

If you post the real table structure, I’ll write the correct SQL for that table.
If you run the queries multiples, it will not change anything additional after the first change.

Related Posts: