From: Rogan Hamby <rhamby@equinoxinitiative.org>
Date: Wed, 17 Jun 2020 13:09:04 +0000 (-0400)
Subject: add mysql function to remove non-alphanumeric characters
X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=b8919f61b86b0c683ec113d77ceddbd2771dcfed

add mysql function to remove non-alphanumeric characters
---

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 ;