mysql regex replace

1REGEXP_REPLACE(expression, pattern, replacement[, position[, occurrence[, match_type]]])
2
3Example 1 - remove all "-" characters
4SELECT REGEXP_REPLACE( fieldname, '-', '' ) AS newfieldname FROM tablename

1WITH t AS (SELECT 'aaa <b>bbb</b> ccc' AS teststring FROM dual)
2
3SELECT
4  teststring,
5  regexp_replace(teststring, '<.+>') AS reg1,
6  regexp_replace(teststring, '<.*>') AS reg2,
7  regexp_replace(teststring, '<.*?>') AS reg3
8FROM t
9
10
11TESTSTRING             REG1        REG2          REG3
12aaa <b>bbb</b> ccc     aaa ccc     aaa ccc       aaa bbb ccc
13

1DELIMITER $$
2
3CREATE FUNCTION  `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
4RETURNS VARCHAR(1000)
5DETERMINISTIC
6BEGIN 
7 DECLARE temp VARCHAR(1000); 
8 DECLARE ch VARCHAR(1); 
9 DECLARE i INT;
10 SET i = 1;
11 SET temp = '';
12 IF original REGEXP pattern THEN 
13  loop_label: LOOP 
14   IF i>CHAR_LENGTH(original) THEN
15    LEAVE loop_label;  
16   END IF;
17   SET ch = SUBSTRING(original,i,1);
18   IF NOT ch REGEXP pattern THEN
19    SET temp = CONCAT(temp,ch);
20   ELSE
21    SET temp = CONCAT(temp,replacement);
22   END IF;
23   SET i=i+1;
24  END LOOP;
25 ELSE
26  SET temp = original;
27 END IF;
28 RETURN temp;
29END$$
30
31DELIMITER ;