mySQL 设置了一个没有特殊字符的 varchar [英] mySQL set a varchar without the special characters

问题描述

我使用 mySQL 作为 DBMS，我的表中有这些行:

I use mySQL as a DBMS, I have these rows in my table:

product_name | product_code | prod_type
prod1@00X    |  1           |    
@prod2@00X   |  2           | 
+prod3@@00X  |  3           | 

我想设置 prod_type = product_name 没有特殊字符.

I wanna set the prod_type = the product_name without the special characters.

=> prod_type

   prod100X
   prod200X
   prod300X

(除了‘@’和‘+’，我可以有其他特殊字符)

(I can have other special characters not only '@' and '+')

我该怎么做?

推荐答案

方法一:

您可以使用 REPLACE() mysql中去除特殊字符的方法，不知道是不是很有效.但它应该可以工作.

You can use the REPLACE() method to remove special characters in mysql, don't know if it's very efficient though. But it should work.

如下图:

SELECT Replace(Replace(product_name,'@',''),'+','')  as prod_type
From Table1

小提琴演示

方法二:

如果您有所有其他特殊字符，请使用此 (来源)

If you have All other Special Charcter then go with this (Source)

-- ---------------------------- 
-- Function structure for `udf_cleanString` 
-- ---------------------------- 
DROP FUNCTION IF EXISTS `udf_cleanString`; 
DELIMITER ;; 
CREATE FUNCTION `udf_cleanString`(`in_str` varchar(4096)) RETURNS varchar(4096) CHARSET utf8 
BEGIN    
      DECLARE out_str VARCHAR(4096) DEFAULT ''; 
      DECLARE c VARCHAR(4096) DEFAULT ''; 
      DECLARE pointer INT DEFAULT 1; 

      IF ISNULL(in_str) THEN 
            RETURN NULL; 
      ELSE 
            WHILE pointer <= LENGTH(in_str) DO 

                  SET c = MID(in_str, pointer, 1); 

                  IF ASCII(c) > 31 AND ASCII(c) < 127 THEN 
                        SET out_str = CONCAT(out_str, c); 
                  END IF; 

                  SET pointer = pointer + 1; 
            END WHILE; 
      END IF; 

      RETURN out_str; 
END 
;; 
DELIMITER ;

之后只需按如下方式调用函数:

After that just call the function as follows:

SELECT product_name, udf_cleanString(product_name) AS 'product_Type' 
FROM table1;

这篇关于mySQL 设置了一个没有特殊字符的 varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！