mySQL set a varchar without the special characters -

i use mysql dbms, have these rows in table:

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

i wanna set prod_type = product_name without special characters.

=> prod_type     prod100x    prod200x    prod300x 

(i can have other special characters not '@' , '+')

how can that?

method 1:

you can use replace() method remove special characters in mysql, don't know if it's efficient though. should work.

like below:

select replace(replace(product_name,'@',''),'+','')  prod_type table1 

fiddle demo

method 2:

if have other special charcter go (source)

-- ----------------------------  -- function structure `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)              return null;        else              while pointer <= length(in_str)                     set c = mid(in_str, pointer, 1);                     if ascii(c) > 31 , ascii(c) < 127                          set out_str = concat(out_str, c);                    end if;                     set pointer = pointer + 1;              end while;        end if;         return out_str;  end  ;;  delimiter ; 

after call function follows:

select product_name, udf_cleanstring(product_name) 'product_type'  table1;