Remove ALL or particular Non printable character from column in mysql

Question

I want to remove all OR particular non printable character from my column in mysql. I think this can be achieve using regexp_replace() function but how that I dont know. Non Printable characters has Ascii value from o to 31. I had Think one solution which is as below: IF I write the function that read all characters from the input string one by one and convert into ASCII. Then every-time I compare this Ascii value with input ascii value and if it matches then replace it and my function will return replaced string. But in my application data is always in bulk so I think It will consume to much time for processing even though I use select query and my user defined function. So I want alternative way to perform this task. I think regexp_replace() will be great but I dont know How to use it

Please help

Thank You, Ronak

Answer 1

DROP function IF EXISTS mysql_replaceallnonprintablecharacters; 

CREATE function mysql_replaceallnonprintablecharacters (data VARCHAR(1024)) 
returns VARCHAR(1024) 
begin 
  DECLARE i INT DEFAULT 0; 

  DECLARE finaldata VARCHAR(1024) DEFAULT ''; 

  SET FINALDATA:=data; 

  WHILE i < 31 do 
    SET FINALDATA:=REPLACE(finaldata, CHAR(i), ''); 
    SET i := i+1; 
  end WHILE; 

  RETURN finaldata; 
end