Replace first instance only of a substring

from the Artful MySQL Tips List


SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS replacefirst;
DELIMITER |
CREATE FUNCTION ReplaceFirst( s VARCHAR(255),old VARCHAR(255),new VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
  DECLARE pos,len SMALLINT;
  SET pos = LOCATE(old,s);
  IF pos <> FALSE THEN
    BEGIN
      SET len = LENGTH(old);
      SET s = CONCAT(SUBSTR(s,1,pos-1),new,SUBSTR(s,pos+len));
    END;
  END IF;
  RETURN s;
END ;
|
DELIMITER ;
bigmutha.sys>SELECT ReplaceFirst('11122333444555666','22','99');
+---------------------------------------------+
| ReplaceFirst('11122333444555666','22','99') |
+---------------------------------------------+
| 11199333444555666                           |
+---------------------------------------------+


Return to the Artful MySQL Tips page