remove all numeric characters from column mysql

I want to develop one mysql function that can remove only numeric characters from the string.

Solution 1:

You can write a user defined function, where in you can write your logic of replacement or you can try :

Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(column,'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1',''),'0','')

Solution 2:

Create function to achieve this task.

DROP FUNCTION IF EXISTS alphas; 
DELIMITER | 
CREATE FUNCTION alphas( str CHAR(32) ) RETURNS CHAR(16) 
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(32) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c REGEXP '[[:alpha:]]' THEN 
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END | 
DELIMITER ; 
SELECT alphas('123ab45cde6789fg'); 
+----------------------------+ 
| alphas('123ab45cde6789fg') | 
+----------------------------+ 
| abcdefg                    | 
+----------------------------+ 

If you want only digits, use this

SET GLOBAL log_bin_trust_function_creators=1; 
DROP FUNCTION IF EXISTS digits; 
DELIMITER | 
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32) 
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(32) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c BETWEEN '0' AND '9' THEN  
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END | 
DELIMITER ; 

SELECT digits('123ab45cde6789fg'); 
+----------------------------+ 
| digits('123ab45cde6789fg') | 
+----------------------------+ 
| 123456789                  | 
+----------------------------+ 

Reference