Functions (MySQL)

Voorbeeld: change_last_substring

Versie 1

CREATE DEFINER=`root`@`localhost` FUNCTION `change_last_substring`(
    input_string text,	
    separator_old tinytext,	
    separator_new tinytext
) RETURNS text CHARSET utf8
BEGIN

set @output_string_right = substring_index(input_string,separator_old,-1);

set @output_string_left=left(input_string,length(input_string)-length(@output_string_right)-2);

RETURN concat
(
   @output_string_left,
   separator_new,
   @output_string_right
);

END

Versie 2 - Extra substitutie

CREATE DEFINER=`root`@`localhost` FUNCTION `change_last_substring`(
    input_string text,	
    separator_old tinytext,	
    separator_new tinytext
) RETURNS text CHARSET utf8
BEGIN

set @output_string_right = substring_index(input_string,separator_old,-1);

set @output_string_left=left(input_string,length(input_string)-length(substring_index(input_string,separator_old,-1))-2);

RETURN concat
(
    @output_string_left,
    separator_new,
    @output_string_right
);

END

Versie 2 - Eén statement

Vernuftig, maar lastig te lezen en te debuggen:

CREATE DEFINER=`root`@`localhost` FUNCTION `change_last_substring`(
	input_string text,	
    separator_old tinytext,	
    separator_new tinytext
) RETURNS text CHARSET utf8
BEGIN

RETURN concat
(
	left(input_string,length(input_string)-length(substring_index(input_string,separator_old,-1))-2),
    separator_new,
    substring_index(input_string,separator_old,-1)
);

END

Versie 3 - Definitief

CREATE DEFINER=`root`@`localhost` FUNCTION `change_last_substring`(
    input_string text,	
    separator_old tinytext,	
    separator_new tinytext
) RETURNS text CHARSET utf8
BEGIN

set @output_string=concat
(
    left(input_string,length(input_string)-length(substring_index(input_string,separator_old,-1))-2),
    separator_new,
    substring_index(input_string,separator_old,-1)
);

RETURN @output_string;

END

Werking:

select(change_last_substring("Aap, Noot, Mies",", "," & "));

Aap, Noot & Mies

Bronnen

• http://www.mysqltutorial.org/mysql-stored-function/