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

possible duplicate of [How to do a regular expression replace in MySQL?](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) — DhruvPathak, Jul 18 '12 at 07:07
possible duplicate of [How to detect and replace non-printable characters from table?](http://stackoverflow.com/questions/11243541/how-to-detect-and-replace-non-printable-characters-from-table) — outis, Jul 18 '12 at 07:56

score 4 · Accepted Answer · edited Apr 23 '13 at 04:07

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

score 2 · Answer 2 · answered Jul 18 '12 at 07:16

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.

answered Jul 18 '12 at 07:16

Tim Pietzcker

328,213
58
503
561

@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. – Tim Pietzcker Jul 18 '12 at 13:50

Remove ALL or particular Non printable character from column in mysql

2 Answers2

Linked