MySQL Utils

translit.sql

DROP FUNCTION IF EXISTS `translit`;
DELIMITER //
CREATE FUNCTION `translit`(`str` TEXT)
	RETURNS text
	LANGUAGE SQL
	DETERMINISTIC
	NO SQL
	SQL SECURITY INVOKER
	COMMENT ''
BEGIN
	DECLARE len INTEGER;
	DECLARE i INTEGER;
	DECLARE dict_from VARCHAR(1024);
	DECLARE dict_to VARCHAR(1024);
	
	SET dict_from = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
	SET dict_to = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';

	IF dict_to IS NOT NULL AND (CHAR_LENGTH(dict_from) != CHAR_LENGTH(dict_to)) THEN
		SET @error = CONCAT('Length of dicts does not match.');
		SIGNAL SQLSTATE '49999'
		SET MESSAGE_TEXT = @error;
	END IF;

	SET len = CHAR_LENGTH(dict_from);
	SET i = 1;

	WHILE len >= i DO
		SET @f = SUBSTR(dict_from, i, 1);
		SET @t = IF(dict_to IS NULL, '', SUBSTR(dict_to, i, 1));
		SET str = REPLACE(str, @f, @t);
		SET i = i + 1;
	END WHILE;

	RETURN str;
END
//
DELIMITER ;