mysql 去掉字符串中的非数字

最简单使用mysql自带的正则替换函数： regexp_replace (值，正则表达式,'')

测试sql: column_name='Aabc6.6e f s'  结果：6.6

SELECT regexp_replace ("Aabc6.6e f s",'[^0-9.]+','')

注意：regexp_replace 函数为 mysql8.0 版本新增,低于8.0版本没有此函数，又不想写自定义函数； 所以自己写了一个处理sql

sql代码

    select GROUP_CONCAT(if(ascii(substring(column_name,help_topic_id+1,1))
    in ('46','48','49','50','51','52','53','54','55','56','57')
    ,substring(column_name,help_topic_id+1,1),'') separator '')
    from mysql.help_topic where help_topic_id < length(column_name)

 测试sql: column_name='Aabc6.6e f s'  结果：6.6

	select GROUP_CONCAT(if(ascii(substring('Aabc6.6e f s',help_topic_id+1,1))
    in ('46','48','49','50','51','52','53','54','55','56','57')
    ,substring('Aabc6.6e f s',help_topic_id+1,1),'') separator '')
    from mysql.help_topic where help_topic_id < length('Aabc6.6e f s')
		
		

生产中查询替换某个列的非数字sql：

	select  tp.column_name /* 原字段值 */

    ,(select GROUP_CONCAT(if(ascii(substring(tp.column_name,help_topic_id+1,1))
    in ('46','48','49','50','51','52','53','54','55','56','57')
    ,substring(tp.column_name,help_topic_id+1,1),'') separator '')
    from mysql.help_topic where help_topic_id < length(tp.column_name)) num  /* 处理后字段值 */

    from (select 'Aabc6.6e f s' column_name ) tp /* 测试的子查询表 */

去掉字符串中的数字，或处理其他的也可以这种模式修改