mysql去掉字符串_MySQL 从字符串中删除不需要的字符

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:

scott建表及录入数据sql脚本

一.需求

将emp表中的元音字母 AEIOU都去除掉

二.解决方案

通过MySQL的replace函数进行多次替换可以解决这个问题

也可以通过MySQL的正则表达式来处理

代码:

select ename,replace(replace(replace(replace(replace(ename,'A',''),'E',''),'I',''),'O',''),'U','') as new_name from emp;

select ename,regexp_replace(ename,'[AEIOU]','') as new_name from emp;

测试记录:

mysql> select ename,replace(replace(replace(replace(replace(ename,'A',''),'E',''),'I',''),'O',''),'U','') as new_name from emp;

+--------+----------+

| ename | new_name |

+--------+----------+

| SMITH | SMTH |

| ALLEN | LLN |

| WARD | WRD |

| JONES | JNS |

| MARTIN | MRTN |

| BLAKE | BLK |

| CLARK | CLRK |

| SCOTT | SCTT |

| KING | KNG |

| TURNER | TRNR |

| ADAMS | DMS |

| JAMES | JMS |

| FORD | FRD |

| MILLER | MLLR |

+--------+----------+

14 rows in set (0.00 sec)

mysql> select ename,regexp_replace(ename,'[AEIOU]') as new_name from emp;

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'regexp_replace'

mysql>

mysql>

mysql>

mysql> select ename,regexp_replace(ename,'[AEIOU]','') as new_name from emp;

+--------+----------+

| ename | new_name |

+--------+----------+

| SMITH | SMTH |

| ALLEN | LLN |

| WARD | WRD |

| JONES | JNS |

| MARTIN | MRTN |

| BLAKE | BLK |

| CLARK | CLRK |

| SCOTT | SCTT |

| KING | KNG |

| TURNER | TRNR |

| ADAMS | DMS |

| JAMES | JMS |

| FORD | FRD |

| MILLER | MLLR |

+--------+----------+

14 rows in set (0.00 sec)

mysql>