Menu
I am trying to strip HTML tags from all of my records directly through MySQL. Thanks to StackOverflow's this question, I found the following function, that kind of does strip html tags -
SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC
BEGIN
DECLARE iStart, iEnd, iLength int;
WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
BEGIN
SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
SET iLength = ( iEnd - iStart) + 1;
IF iLength > 0 THEN
BEGIN
SET Dirty = Insert( Dirty, iStart, iLength, '');
END;
END IF;
END;
END WHILE;
RETURN Dirty;
END;
|
DELIMITER ;
SELECT fnStripTags('this <html>is <b>a test</b>, nothing more</html>');
But I am unable to find out, How to use this function to update all records. For e.g. I have records in Address column of myTable, from which I want to strip HTML tags using above mentioned function. How can I directly update all records of the Address column with the help of mentioned function, or if direct update is not possible, then any way to insert all updated records in a 2nd column of the table Address_Stripped?
P.S., I know, my question lacks any research done to get the answer by myself, but it's simply because I don't know how to search for it.
529
You just need to call this function in an update statement:
UPDATE mytable
SET address = fnStripTags(address)
It depends on how many rows you should update and can or can not you lock the entire table for the update time.
If the table is rather small or can be locked for the update time:
UPDATE table_name
SET address = fnStripTags(address)
And if the table is big and/or you can not lock the table for the entire update time - you should perform those updates in loop, by chunks
with ORDER BY primary_key
UPDATE table_name
SET address = fnStripTags(address)
WHERE primary_key > <previous_value>
ORDER BY primary_key
LIMIT 1000
(you can use any suitable limit)
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
