• notice
  • Congratulations on the launch of the Sought Tech site

How to implement regex_replace regular replacement function in mysql5.7

PS: I recently received a small request: for desensitization of user sensitive information, the data source is mysql. The initial idea was to use the regexp_replace function in mysql to clean sensitive fields directly. Later, it was found that regexp_replace() was only supported by mysql8.0. There are only regular matching functions in mysql5.7. Later, I want to extract the data to hdfs and use hive to process it. When I thought that I had to build a table, I finally decided to use mysql's custom function to solve it, saving time and effort.

1. Enable mysql custom function support

a. Check whether mysql currently supports writing custom:

show variables like '%fun%'; 

20191121153456833.png

b.log_bin_trust_function_creators=OFF means that the custom function is not enabled. Enter the start command.

set global log_bin_trust_function_creators=1; 

20191121154052158.png

Note: It is not turned on here, and the custom function will be written later. call doesn't work!

2. Write regexp_replace() custom function

a. Write the code as follows:

#Delete the created custom function before creating
drop function if exists regexp_replace;
#Create regexp_replace function
DELIMITER $$
CREATE FUNCTION `regexp_replace`(string_a VARCHAR(1000), pattern VARCHAR(1000), string_b VARCHAR(1000))
  RETURNS VARCHAR(1000)
  DETERMINISTIC
BEGIN
 DECLARE string_c VARCHAR(1000);
 DECLARE nub VARCHAR(1);
 DECLARE i INT;
 SET i =1;
 SET string_c = '';
 IF string_a REGEXP pattern THEN
    loop_label: LOOP
      IF i>CHAR_LENGTH(string_a) THEN
        LEAVE loop_label;
 END IF;
 SET nub = SUBSTRING(string_a,i,1);
 IF NOT nub REGEXP pattern THEN
 SET string_c = CONCAT(string_c,nub);
      ELSE
        SET string_c = CONCAT(string_c,string_b);
      END IF;
      SET i=i+1;
    END LOOP;
  ELSE
    SET string_c = string_a;
  END IF;
  RETURN string_c;
END$$
DELIMITER;

b. After the above code is executed, check whether the creation is successful. The result has been created successfully.
20191121160626376.png

c. Test the function and the result is successful!
20191121161738758.png


Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+