mysql 正则+function

SELECT JOB_NO,CUSTOMER_REF_NO,LENGTH(CUSTOMER_REF_NO) FROM OMS_JOB A
 WHERE A.CUSTOMER_REF_NO REGEXP '[[:cntrl:]]'

 

 

drop function mytest;
DELIMITER //
CREATE FUNCTION mytest(str varchar(500))
RETURNS VARCHAR(500)
BEGIN
 DECLARE l_new_string VARCHAR(500);
 DECLARE sizestr int;
 DECLARE posstr int;
 declare nch varchar(100);
 set sizestr = length(str);
 set posstr = 1;
 set l_new_string = ' '; 
 
 WHILE posstr<sizestr+1 DO 
        SET nch = SUBSTRING(str,posstr,1); 
        SET l_new_string = concat(l_new_string,'--',ASCII(nch)); 
        SET posstr = posstr + 1;
   end WHILE;     
RETURN l_new_string;
END;//