Technically not a user defined aggregate function, and not optimal, but this works and doesn't need to create an UDF: use GROUP_CONCAT()
in your query which will make your query an aggregate one and will consolidate all values in one comma-separated string, and then write a SQL function that processes that string.
Example (function based on this answer)::
DELIMITER $$
CREATE FUNCTION `replace_multiple`(_text text, _from text, _to text) RETURNS text CHARSET utf8mb4
DETERMINISTIC
BEGIN
DECLARE _nextfrom TEXT DEFAULT NULL;
DECLARE _nextto TEXT DEFAULT NULL;
DECLARE _nextlenfrom INT DEFAULT NULL;
DECLARE _nextlento INT DEFAULT NULL;
DECLARE _valuefrom TEXT DEFAULT NULL;
DECLARE _valueto TEXT DEFAULT NULL;
iterator:
LOOP
-- exit the loop if the list seems empty or was null;
-- this extra caution is necessary to avoid an endless loop in the proc.
IF CHAR_LENGTH(TRIM(_from)) = 0 OR _from IS NULL THEN
LEAVE iterator;
END IF;
-- capture the next value from the list
SET _nextfrom = SUBSTRING_INDEX(_from,',',1);
SET _nextto = SUBSTRING_INDEX(_to,',',1);
-- save the length of the captured value; we will need to remove this
-- many characters + 1 from the beginning of the string
-- before the next iteration
SET _nextlenfrom = CHAR_LENGTH(_nextfrom);
SET _nextlento = CHAR_LENGTH(_nextto);
-- trim the value of leading and trailing spaces, in case of sloppy CSV strings
SET _valuefrom = TRIM(_nextfrom);
SET _valueto = TRIM(_nextto);
-- use the extracted value
SET _text = replace(_text, _valuefrom, _valueto);
-- rewrite the original string using the `INSERT()` string function,
-- args are original string, start position, how many characters to remove,
-- and what to "insert" in their place (in this case, we "insert"
-- an empty string, which removes _nextlen + 1 characters)
SET _from = INSERT(_from,1,_nextlenfrom + 1,'');
SET _to = INSERT(_to,1,_nextlento + 1,'');
END LOOP;
RETURN _text;
END$$
DELIMITER ;
Table t:
id |
fromThis |
toThat |
1 |
1 |
one |
2 |
2 |
two |
3 |
3 |
three |
Query:
SELECT replace_multiple('my 2 cents', GROUP_CONCAT(fromThis), GROUP_CONCAT(toThat))
FROM t;
Will translate to:
SELECT replace_multiple('my 2 cents', '1,2,3', 'one,two,three')
Which for this specific custom function will return my two cents