如何删除mysql表中的数字字符? [英] how to Remove Numeric characters in mysql's table?
本文介绍了如何删除mysql表中的数字字符?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 MySQL 中有一个名为Actress
"的表.
我想从列name
"
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)
如何更新我的表以删除 MySQL 表中的数字字符,以便获得如下结果
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_ |
+-------+---------------------+
推荐答案
它看起来不太好,但确实有效.它从字符串中删除任何数字
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','');
如果你使用 MariaDB,你可以使用 REGEX_REPLACE:
IF you use MariaDB you can use REGEX_REPLACE:
update Actress
set name = REGEXP_REPLACE(name,'[0-9]','');
示例
MariaDB [(none)]> SELECT REGEXP_REPLACE('A1B2C44','[0-9]','');
+--------------------------------------+
| REGEXP_REPLACE('A1B2C44','[0-9]','') |
+--------------------------------------+
| ABC |
+--------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
这篇关于如何删除mysql表中的数字字符?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文