How to run mySQL function to update all rows?

Question

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.

score 7 · Accepted Answer · answered Aug 12 '15 at 07:30

7

You just need to call this function in an update statement:

UPDATE mytable
SET    address = fnStripTags(address)

answered Aug 12 '15 at 07:30

Mureinik

297,002
52
306
350

Thanks. It worked. SO timelimit is preventing me to accept it as answer (8 minutes remaining to accept as answer) – Dr. Atul Tiwari Aug 12 '15 at 07:34

score 3 · Answer 2 · answered Aug 12 '15 at 07:44

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)

Thanks for your answer. I got your point. But I already accepted the answer (which is actually correct, just that yours one has addition info.) approx. same time you posted it. Upvote from me :) — Dr. Atul Tiwari, Aug 12 '15 at 07:49

How to run mySQL function to update all rows?

2 Answers2