Kunal Batra Kunal Batra - 7 months ago 15
SQL Question

how to Remove Numeric characters in mysql's table?

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_ |
+-------+---------------------+

Answer

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)]>
Comments