From b8919f61b86b0c683ec113d77ceddbd2771dcfed Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Wed, 17 Jun 2020 09:09:04 -0400 Subject: [PATCH] add mysql function to remove non-alphanumeric characters --- kmig.d/sql/init/020-string_functions.sql | 21 +++++++++++++++++++++ 1 files changed, 21 insertions(+), 0 deletions(-) diff --git a/kmig.d/sql/init/020-string_functions.sql b/kmig.d/sql/init/020-string_functions.sql index 22469cd..4741c90 100644 --- a/kmig.d/sql/init/020-string_functions.sql +++ b/kmig.d/sql/init/020-string_functions.sql @@ -41,3 +41,24 @@ CREATE FUNCTION $ DELIMITER ; +DROP FUNCTION IF EXISTS m_remove_nonalpha; +delimiter $ +CREATE FUNCTION m_remove_nonalpha( s CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC + BEGIN + DECLARE var1, length SMALLINT DEFAULT 1; + DECLARE result CHAR(255) DEFAULT ''; + DECLARE ch CHAR(1); + SET length = CHAR_LENGTH( s ); + REPEAT + BEGIN + SET ch = MID( s, var1, 1 ); + IF ch REGEXP '[[:alnum:]]' THEN + SET result =CONCAT(result ,ch); + END IF; + SET var1 = var1 + 1; + END; + UNTIL var1 >length END REPEAT; + RETURN result ; + END + $ + DELIMITER ; -- 1.7.2.5