如何从批量数据中的MySQL列中删除所有非数字字符 [英] How to remove all the non numeric characters from column in mysql in bulk data

问题描述

我想从列中删除所有非数字字符.我的数据库中有大量数据.

I want to remove all the non numeric characters from the column. I have bulk data in my database.

目前，我正在使用以下链接中所述的方法:

Currently I am using method as describe in below link:

http://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/

问题在于，它需要太多的时间进行处理.

The problem is that its taking too much time for preocessing.

一百万行电流逻辑需要1个小时来处理数据.

For 1 million of row current logic takes 1 hour to process the data.

请帮助我.

谢谢， 罗纳克

推荐答案

这又是另一件事……

演示:: http://sqlfiddle.com/#!2/0c96e/21

首先，创建一个数字表

CREATE TABLE numbers (
   number int NOT NULL PRIMARY KEY
);

INSERT INTO numbers (number)
SELECT n0 + n1 + n2 + n3 + n4 + n5
FROM   (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS z0
 CROSS
  JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT 12) AS z1
 CROSS
  JOIN (SELECT 0 AS n2 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48) AS z2
 CROSS
  JOIN (SELECT 0 AS n3 UNION SELECT 64 UNION SELECT 128 UNION SELECT 192) AS z3
 CROSS
  JOIN (SELECT 0 AS n4 UNION SELECT 256 UNION SELECT 512 UNION SELECT 768) AS z4
 CROSS
  JOIN (SELECT 0 AS n5 UNION SELECT 1024 UNION SELECT 2048 UNION SELECT 3072) AS z5
ORDER
    BY 1;

这里有一些示例数据可供使用

Here's some sample data to play with

CREATE TABLE your_table (
   foo varchar(50)
);

INSERT INTO your_table (foo)
  VALUES ('124nhasfonasf13')
       , ('NONE')
       , ('r937')
       , ('o9o9')
       , ('n444n4n455n')
       , ('blah');

然后这是一个查询，只给您数字.应该更有效，因为它是基于SET的，而不是像函数示例一样是迭代的...

Then here's a query to give you just the numbers. Should be more efficient as it is SET based instead of iterative like your function example...

SELECT foo
     , Group_Concat(c ORDER BY position SEPARATOR '')
FROM   (
        SELECT vals.foo
             , numbers.number As position
             , SubString(vals.foo, numbers.number, 1) As c
        FROM   (
                SELECT foo
                     , Length(foo) As lngth
                FROM   your_table
                WHERE  foo REGEXP '[0-9]'
               ) As vals
         INNER
          JOIN numbers
            ON numbers.number BETWEEN 1 AND vals.lngth
       ) As x
WHERE  c REGEXP '[0-9]'
GROUP
    BY foo

这篇关于如何从批量数据中的MySQL列中删除所有非数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！