remove all numeric characters from column mysql

Question

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

Welcome to SO. Please show us your code and let us know where you have stucked. We will surely help you. — Fahim Parkar
– Fahim Parkar, Commented Jul 11, 2012 at 12:11

Sashi Kant · Accepted Answer · 2016-01-19 05:18:20Z

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','')

edited Jan 19, 2016 at 5:18

answered Jul 11, 2012 at 11:41

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Fahim Parkar Over a year ago

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 Over a year ago

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

mooreds Over a year ago

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 Over a year ago

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

Kzqai Over a year ago

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.

|

Fahim Parkar · Accepted Answer · 2012-07-11 12:32:06Z

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

answered Jul 11, 2012 at 12:32

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

2 Comments

Ronak Shah Over a year ago

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

Fahim Parkar Over a year ago

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

Collectives™ on Stack Overflow

remove all numeric characters from column mysql

2 Answers 2

8 Comments

Reference

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related