9

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

Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276
Ronak Shah
  • 1,539
  • 2
  • 13
  • 20

2 Answers2

9

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','')
Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
  • that means if I need to remove characters from string, I will have to write replace 52 (26 for lower and 26 for upper case)?? – Fahim Parkar Jul 11 '12 at 12:22
  • NO then we should opt for a udf..it was just an alternative – Sashi Kant Jul 11 '12 at 12:23
  • Yes Sashi kant It works.. Thanx a Lot for reply.. Can we achieve same solution with any regular expression? – Ronak Shah Jul 12 '12 at 04:32
  • 2
    Note that I wasn't able to make REPLACE(column,'[0-9]+','') work with mysql 5.1. I didn't try the alternative answer provided. – mooreds May 24 '13 at 03:01
  • 2
    Sadly, `mysql`, unlike `postgresql`, does not support regular expression replace out of the box. – Kzqai Mar 12 '14 at 22:12
  • 3
    Uh, then you agree that the above answer, as written, is misleading, and should be changed? http://sqlfiddle.com/#!2/5ae225/3/1 The statement executes, but it only actually tries to replace the literal string '[0-9]+', which is obviously never going to be found. – Kzqai Mar 13 '14 at 15:18
  • Thanks for the alternative, it saved me a lot of typing ;) The first one sadly doesn't work though... – oriadam Oct 15 '15 at 13:07
  • This is kinda trivial. Isn't there a more elegent answer in MySQL? – Scott Chu May 31 '17 at 08:24
6

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

Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276