How to remove numeric characters in mysql table?

Question

I have one table named " Actress " in MySQL.
I want to remove all numeric characters from the " name " column

 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 to update a table to remove numeric characters in a MySQL table to get the result as shown below

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

Answer 1

It doesn’t look very good, 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]',''); 

Example

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

Answer 2

Hi, I got the answer that I performed below the queries, and this solved my problem of removing all numeric digits from the mysql 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', ''); 

Answer 3

Try SELECT before running the update command. The internal LOCATE function gives the first occurrence '', and the external Locate gives you the second occurrence. LEFT gets you all the characters until the second appearance. '' I have not tried this, so I hope this 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