remove all numeric characters from column mysql

Question

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

Comments

[Fahim Parkar]

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)??

[Sashi Kant]

NO then we should opt for a udf..it was just an alternative

[Ronak Shah]

Yes Sashi kant It works.. Thanx a Lot for reply.. Can we achieve same solution with any regular expression?

[Fahim Parkar]

@RonakShah : Please accept the answer whichever is working. See here, how to accept answer

[Ronak Shah]

thnx Fahim.. Actually I did not know how to accept the answer..Anyway thnx for help

[Fahim Parkar]

@RonakShah : I guessed that as I saw you are new... so sharing that was my job... at start even I was not knowing...

[mooreds]

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.

[Kzqai]

Sadly, mysql, unlike postgresql, does not support regular expression replace out of the box.

[Kzqai]

Uh, then you agree that the above answer, as written, is misleading, and should be changed? 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.

[oriadam]

Thanks for the alternative, it saved me a lot of typing ;) The first one sadly doesn't work though...

[Scott Chu]

This is kinda trivial. Isn't there a more elegent answer in MySQL?