从MySQL的列中删除所有或特定的非可打印字符 [英] Remove ALL or particular Non printable character from column in mysql

问题描述

我想从mysql的列中删除所有OR特定的非可打印字符. 我认为可以使用 regexp_replace()函数来实现，但我不知道如何. 不可打印字符的Ascii值介于o到31之间. 我有一个解决方案，如下所示: 如果我编写了一个函数，该函数从输入字符串中一个接一个地读取所有字符，然后转换为ASCII.然后，每次我将此Ascii值与输入的ascii值进行比较，如果匹配，则将其替换，然后我的函数将返回替换后的字符串. 但是在我的应用程序中，数据始终是大量的，因此我认为即使我使用选择查询和用户定义的函数，也会消耗大量时间进行处理. 因此，我想要执行此任务的替代方法.我认为 regexp_replace()会很棒，但我不知道如何使用它

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

请帮助

谢谢， 罗纳克

推荐答案

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 

这篇关于从MySQL的列中删除所有或特定的非可打印字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！