Useful MySQL Functions

--Validate Email address

DELIMITER $$
DROP FUNCTION IF EXISTS is_valid_email $$
CREATE FUNCTION `is_valid_email`(p_email varchar(150)) RETURNS tinyint(1)
BEGIN
CASE
WHEN NOT (SELECT p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9-]+[\.]{1}[A-Z]{2,4}$')
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END CASE;
END $$
DELIMITER ;

--Only Alphabetical characters

DELIMITER $$
DROP FUNCTION IF EXISTS is_alphabetical_string $$
CREATE FUNCTION `is_alphabetical_string`(p_string varchar(150)) RETURNS tinyint(1)
BEGIN
CASE
WHEN NOT (SELECT p_string REGEXP '^[A-Za-z]*$')
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END CASE;
END $$
DELIMITER ;

--Only Digits

DELIMITER $$
DROP FUNCTION IF EXISTS is_digit_string $$
CREATE FUNCTION `is_digit_string`(p_string varchar(150)) RETURNS tinyint(1)
BEGIN
CASE
WHEN NOT (SELECT p_string REGEXP '^[0-9]*$')
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END CASE;
END $$
DELIMITER ;

--Only Alphanumeric characters

DELIMITER $$
DROP FUNCTION IF EXISTS strip_non_alpha $$
CREATE FUNCTION `strip_non_alpha`(_dirty_string varchar(110)) RETURNS varchar(110)
BEGIN
DECLARE _length int;
DECLARE _position int;
DECLARE _current_char varchar(1);
DECLARE _clean_string varchar(110);
SET _clean_string = '';
SET _length = LENGTH(_dirty_string);
SET _position = 1;
WHILE _position <= _length DO
SET _current_char = SUBSTRING(_dirty_string, _position, 1);
IF _current_char REGEXP '[A-Za-z0-9]' THEN
SET _clean_string = CONCAT(_clean_string, _current_char);
END IF;
SET _position = _position + 1;
END WHILE;
RETURN CONCAT('', _clean_string);
END $$
DELIMITER ;

--Get a sample from a data set

DELIMITER $$
DROP PROCEDURE IF EXISTS staging.get_random_sample $$
CREATE PROCEDURE staging.get_random_sample (p_source_schema varchar(64),p_source_table varchar(64),p_target_schema varchar(64),p_target_table varchar(64),p_percentage int)
BEGIN
DECLARE v_source_num_rows int DEFAULT 0;
DECLARE v_target_num_rows int DEFAULT 0;
DECLARE v_row_num int DEFAULT 0;
DECLARE i int DEFAULT 0;
SET @select_stmt = concat('SELECT COUNT(*) INTO @v_source_num_rows',' FROM ',p_source_schema,'.',p_source_table);
prepare select_stmt from @select_stmt;
execute select_stmt;
SET @v_target_num_rows = ROUND(@v_source_num_rows*p_percentage/100);
SET @create_stmt = concat('CREATE TABLE ',p_target_schema,'.',p_target_table,' LIKE ',p_source_schema,'.',p_source_table);
prepare create_stmt from @create_stmt;
execute create_stmt;
WHILE @v_target_num_rows > i DO
SELECT FLOOR(1+RAND() * @v_source_num_rows) - 1 INTO v_row_num;
SET @insert_stmt = concat('INSERT INTO ',p_target_schema,'.',p_target_table,' SELECT * FROM ',p_source_schema,'.',p_source_table,' LIMIT ',v_row_num,'\,1');
prepare insert_stmt from @insert_stmt;
execute insert_stmt;
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;