生成测试数据

create view v as

select ename as data from emp where deptno=10 union all

select concat(ename,', $',sal,'.00') as data from emp where deptno=20 union all

select concat(ename,deptno) as data from emp where deptno=30;

mysql> select * from v;

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

| data     |

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

| CLARK    |

| KING     |

| MILLER   |

| ALLEN30  |

| WARD30   |

| MARTIN30 |

| BLAKE30  |

| TURNER30 |

| JAMES30  |

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

 

解决方案：

mysql> select data from v

where data regexp '[^0-9a-zA-Z]' = 0 ;

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

| data     |

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

| CLARK    |

| KING     |

| MILLER   |

| ALLEN30  |

| WARD30   |

| MARTIN30 |

| BLAKE30  |

| TURNER30 |

| JAMES30  |

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