Search code examples
mysqlinformation-schema

Replace function set as Store Procedure or Function in MySQL?


How can I convert my replace function as a stored procedure or function in MySQL?

Here is a simplified update script:

mysql> UPDATE content SET url = REPLACE (url, 'testsite', 'livesite') WHERE URL LIKE '%testsite%';

I want to be able to store this and run this function via a query.

My goal is to to something like this:

CALL myupdatefunction ('testsite', 'livesite', @url);

This is what I have tried so far:

DELIMITER //
CREATE PROCEDURE updateURL(OUT url1, OUT url2)
BEGIN 
   UPDATE content SET url = REPLACE (url, url1, url2) WHERE URL LIKE url1;
END //
DELIMITER ;

And to CALL

CALL updateURL('stringOld','stringNew');

Not sure if this is the right approach?


Solution

  • You just have to make a little change, you have to add the percent sign in the like expression

    DELIMITER //
    CREATE PROCEDURE updateURL(OUT url1, OUT url2)
    BEGIN 
       UPDATE content SET url = REPLACE (url, url1, url2) WHERE URL LIKE CONCAT('%', url1, '%');
    END //
    DELIMITER ;