问题：如果数值与字符混合在一起，需要删除那些字符，只返回数字。

create view v as 

select concat(

substr(ename,1,2),

replace(cast(deptno as char(4)),' ',''),

substr(ename,3,2)

) as mixed

from emp

where deptno=10

union all

select replace(cast(empno as char(4)),' ','')

from emp where deptno=20

union all

select ename from emp where deptno=30;

select * from v;

+--------+

| mixed  |

+--------+

| CL10AR |

| KI10NG |

| MI10LL |

| ALLEN  |

| WARD   |

| MARTIN |

| BLAKE  |

| TURNER |

| JAMES  |

+--------+

 

解决方案：

select cast(group_concat(c order by pos separator '' ) as unsigned) as MIXED1

from (

select v.mixed,iter.pos,substr(v.mixed,iter.pos,1) as c

from v,

(select id pos from t10)iter

where iter.pos <=length(v.mixed)

and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57

)y 

group by mixed

order by 1;

+--------+

| MIXED1 |

+--------+

|     10 |

|     10 |

|     10 |

+--------+