DROP FUNCTION IF EXISTS FORWARDERS_OUT ;

DELIMITER |

CREATE FUNCTION FORWARDERS_OUT (email_str TEXT, vacation_domain TEXT , list_seperator CHAR)
   RETURNS TEXT
   DETERMINISTIC
   BEGIN
      DECLARE forward_str TEXT;
      DECLARE local_email_part TEXT;
      DECLARE domain_email_part TEXT;
      DECLARE first_char CHAR;
      DECLARE last_char CHAR;

      -- get list of forwarders
      --
      SELECT goto INTO forward_str FROM alias WHERE address=email_str;

      -- entferne mailbox emailadresse
      --
      SET forward_str =  REPLACE(forward_str, email_str, '' );
      -- SELECT REPLACE(forward_str, email_str, '' ) INTO forward_str;

      -- entferne vacation adresse
      --
      SET local_email_part = SUBSTRING(email_str,1, LOCATE('@',email_str) - 1);
      SET domain_email_part = SUBSTRING(email_str, LOCATE('@',email_str) + 1, LENGTH(email_str));
      SET forward_str = REPLACE(forward_str, CONCAT(local_email_part, "#" ,domain_email_part,"@", vacation_domain), '');
      -- SELECT REPLACE(forward_str, CONCAT(list_seperator,list_seperator), list_seperator) INTO forward_str;

      -- enferne doppelte seperatorzeichen
      --
      WHILE LOCATE(CONCAT(list_seperator,list_seperator) , forward_str) DO
         SET forward_str = REPLACE(forward_str, CONCAT(list_seperator,list_seperator), list_seperator);
         -- SELECT REPLACE(forward_str, CONCAT(list_seperator,list_seperator), list_seperator) INTO forward_str;
      END WHILE ;

      -- entferne erstes zeichen wenn es das seperatorzeichen ist
      --
      IF LEFT(forward_str,1) = list_seperator THEN
         SET forward_str =  SUBSTRING(forward_str FROM 2);
         -- SELECT SUBSTRING(forward_str FROM 2) INTO forward_str;
      END IF;

      -- entferne letztes zeichen wenn es das seperatorzeichen ist
      --
      IF RIGHT(forward_str,1) = list_seperator THEN
         SET forward_str = SUBSTRING(forward_str , 1, LENGTH(forward_str) - 1);
         -- SELECT SUBSTRING(forward_str , 1, LENGTH(forward_str) - 1) INTO forward_str;
      END IF;

      RETURN forward_str;
   END |

DELIMITER ;