sql query to put quotes around numbers in img tag

Question

I have quite a few old posts that contain an img tag with height and width with no double quotes around the numbers. For example:

<img height=319 alt="" src="http://www.example.com/images/myexample.jpg" width=496>

I am not sure how to write a MySQL query that can find the height and width numbers, and surround them with double quotes.

Although a MySQL query is preferred, if this is not possible then perhaps someone could suggest a regular expression I could use with a PHP script that might solve this issue.

Answer 1

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.

Answer 2

Here's your started for 10...

You can use the REGEXP operator in a MySQL query, then do an update on the returned results.

The regular expression you need will be something like:

width=([0-9]*)

So you're query will be something like:

SELECT * FROM table WHERE column REGEXP "width=([0-9]*)"

The reason I'm using the terminology "something like" is that i can not test this on a database.

Further reading for you:

http://www.regular-expressions.info/mysql.html

Hope this helps.