Remove ALL or particular Non printable character from column in mysql

12,805

Solution 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 

Solution 2

MySQL doesn't support regex replace operations natively, only searches.

That said, there are packages that do provide some functionality like Oracle's REGEXP_REPLACE() as User defined functions.

The regular expression [[:cntrl:]]+ matches one or more non-printable characters (ASCII 0-31 and ASCII 127).

So, using the abovementioned package, REGEXP_REPLACE?(text, "[[:cntrl:]]+", "") will modify text by stripping it of all non-printable characters.

Share:
12,805
Ronak Shah
Author by

Ronak Shah

I am software developer. my email address is [email protected]

Updated on June 04, 2022

Comments

  • Ronak Shah
    Ronak Shah over 1 year

    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

  • Tim Pietzcker
    Tim Pietzcker over 11 years
    @RonakShah: You might want to post this as an answer if that is the solution that worked best for you. You can accept your own answer, too. You just can't upvote your own posts, only those of others.