MySQL: How can I remove trailing HTML from a field in the database?

Question

I want to remove some rogue HTML from a DB field that is supposed to contain a simple filename. Example of ok field:

myfile.pdf

Example of not ok field:

myfile2.pdf<input type="hidden" id="gwProxy" />...

Does anyone know a query I can run that can remove the HTML part but leave the filename? i.e. remove everything from the first < character onwards.

Lets assume the field is called myattachment and is defined as a varchar(250) and the table is called mytable in a MySQL database.

Background info (not necessary to read):

The field in our database is supposed to contain filenames however, due to a issue (documented here) some of the fields now contain a filename and some rogue HTML. We have fixed the root issue and now need to fix the corrupt fields. In the past I have replaced text using this kind of query:

UPDATE mytable SET myattachment = replace(myattachment, 'JPG', 'jpg') WHERE myattachment LIKE '%JPG';

How many rows does the table have? If not over 1million I suggest extracting the id and myattachment to a file. Use a text editor with regular expression to do the replace and import back the column. — Yada
– Yada, Commented Sep 9, 2010 at 17:48
Thanks for your suggestion. I did consider fixing the issue using a PHP script to read the rows, fix the corruption and write them back. But then I thought there must be a MySQL query I can run that will be quicker? — Tom
– Tom, Commented Sep 9, 2010 at 17:56

OMG Ponies · Accepted Answer · 2010-09-09 17:59:08Z

1

This query seems to work ok, can anyone see any issues with it?

UPDATE mytable
   SET myattachment = SUBSTRING_INDEX(myattachment, '<', 1) 
 WHERE `myattachment` LIKE '%<%';

For docs on SUBSTRING_INDEX see the mysql manual page.

edited Sep 9, 2010 at 17:59

OMG Ponies

334k85 gold badges536 silver badges509 bronze badges

answered Sep 9, 2010 at 17:54

Tom

14.3k16 gold badges69 silver badges81 bronze badges

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

Collectives™ on Stack Overflow

MySQL: How can I remove trailing HTML from a field in the database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related