how to Remove Numeric characters in mysql's table?

Question

I have one table with name "Actress" in MySQL.
I want to remove all numeric character from column "name"

select * from Actress  limit 5;
+-------+---------------------+
| code  | name                |
+-------+---------------------+
| 11455 | Hanshika_Motwani_19 |
| 11457 | Kajal_Agrwal_11     |
| 11458 | Ileana_21           |
| 11459 | Kaveri_Jha_11       |
| 11462 | Kaveri_Jha_18       |
+-------+---------------------+
5 rows in set (0.00 sec)

How can I update my table to remove Numeric characters in MySQL's table so that I could get the result like below

select * from Actress  limit 5;
+-------+---------------------+
| code  | name                |
+-------+---------------------+
| 11455 | Hanshika_Motwani_   |
| 11457 | Kajal_Agrwal_       |
| 11458 | Ileana_21           |
| 11459 | Kaveri_Jha_         |
| 11462 | Kaveri_Jha_         |
+-------+---------------------+

Do the numbers always appear at the end of the `name` column? — Tim Biegeleisen, Apr 08 '16 at 06:11
Possible duplicate of [Removing numbers from string in mysql](http://stackoverflow.com/questions/13855053/removing-numbers-from-string-in-mysql) — Tim Biegeleisen, Apr 08 '16 at 06:13
hey thanx for the reply @Tim Biegeleisen i have posted my answer thats solved my problem — Kunal Batra, Apr 08 '16 at 06:17

Bernd Buffen · Accepted Answer · 2016-04-25T18:52:21.600

It look not very nice, but it works. It Removes any Digit from the string

SELECT
    REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE (
    REPLACE( REPLACE( REPLACE( REPLACE('Hallo_1234567890_99','0','')
    ,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','');


update Actress 
SET name  = REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE (
        REPLACE( REPLACE( REPLACE( REPLACE(name,'0','')
        ,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','');

IF you use MariaDB you can use REGEX_REPLACE:

update Actress
 set name =  REGEXP_REPLACE(name,'[0-9]','');

Sample

MariaDB [(none)]> SELECT REGEXP_REPLACE('A1B2C44','[0-9]','');
+--------------------------------------+
| REGEXP_REPLACE('A1B2C44','[0-9]','') |
+--------------------------------------+
| ABC                                  |
+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

score 3 · Answer 2 · answered Apr 08 '16 at 06:15

Hey i got an answer i have executed below queries and thats solved my problem to remove all numeric digits from mysql's table

update Actress SET name  = REPLACE(name, '1', '');
update Actress SET name  = REPLACE(name, '2', '');
update Actress SET name  = REPLACE(name, '3', '');
update Actress SET name  = REPLACE(name, '4', '');
update Actress SET name  = REPLACE(name, '5', '');
update Actress SET name  = REPLACE(name, '6', '');
update Actress SET name  = REPLACE(name, '7', '');
update Actress SET name  = REPLACE(name, '8', '');
update Actress SET name  = REPLACE(name, '9', '');
update Actress SET name  = REPLACE(name, '0', '');

A bit brute force and draconian, but it gets the job done. – Tim Biegeleisen Apr 08 '16 at 06:17 — Tim Biegeleisen, Apr 08 '16 at 06:17
10 Updates for each row - very hard – Bernd Buffen Apr 25 '16 at 18:48 — Bernd Buffen, Apr 25 '16 at 18:48

score 0 · Answer 3 · answered Apr 08 '16 at 06:24

Try SELECT before you run the update command. The inner LOCATE function gives the first occurrence of '' and the outer Locate gives you the second occurrence. LEFT gets you all characters till the second occurrence of ''. I haven't tried this so hope it works.

SELECT LEFT(name,LOCATE('_',name,LOCATE('_',name)+1)) as name FROM Actress WHERE 1


UPDATE Actress SET name=LEFT(name,LOCATE('_',name,LOCATE('_',name)+1)) WHERE 1

how to Remove Numeric characters in mysql's table?

3 Answers3

Linked