MySQL剥离非数字字符进行比较

Question

我正在寻找在表中查找与用户输入的特定数字匹配的记录。因此，用户可以输入12345，但是在数据库中可以是123zz4-5。我想如果PHP函数可以在MySQL中工作，那么类似的事情就可以工作。SELECT * FROM foo WHERE preg_replace("/[^0-9]/","",bar) = '12345'仅MySQL的等效功能或方法是什么？

Answer 1

我意识到这是一个古老的话题，但是在搜索了这个问题后，我找不到一个简单的解决方案（我看到了古老的代理，但认为这是一个更简单的解决方案），所以这是我编写的一个函数，似乎工作得很好。

DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;

DELIMITER $$

CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))

   RETURNS VARCHAR(255)

BEGIN

   DECLARE output   VARCHAR(255) DEFAULT '';

   DECLARE iterator INT          DEFAULT 1;

   WHILE iterator < (LENGTH(input) + 1) DO

      IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN

         SET output = CONCAT(output, SUBSTRING(input, iterator, 1));

      END IF;

      SET iterator = iterator + 1;

   END WHILE;

   RETURN output;

END

$$

Answer 2

没有regexp替换，只有纯字符串REPLACE（）。

MySQL具有REGEXP运算符，但它只是一个匹配测试器，而不是替换器，因此您必须将逻辑从内到外：

SELECT * FROM foo WHERE bar REGEXP '[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*';

这就像您使用LIKE的版本，但匹配更为准确。两者都将同样表现不佳，需要没有索引的全表扫描。

Answer 3

这是一个改进的版本：

DELIMITER ;;

DROP FUNCTION IF EXISTS `STRIP_NON_DIGIT`;;

CREATE DEFINER=`root`@`localhost` FUNCTION `STRIP_NON_DIGIT`(input VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8

READS SQL DATA

BEGIN

   DECLARE output    VARCHAR(255) DEFAULT '';

   DECLARE iterator  INT          DEFAULT 1;

   DECLARE lastDigit INT          DEFAULT 1;

   DECLARE len       INT;

   SET len = LENGTH(input) + 1;

   WHILE iterator < len DO

      -- skip past all digits

      SET lastDigit = iterator;

      WHILE ORD(SUBSTRING(input, iterator, 1)) BETWEEN 48 AND 57 AND iterator < len DO

         SET iterator = iterator + 1;

      END WHILE;

      IF iterator != lastDigit THEN

         SET output = CONCAT(output, SUBSTRING(input, lastDigit, iterator - lastDigit));

      END IF;

      WHILE ORD(SUBSTRING(input, iterator, 1)) NOT BETWEEN 48 AND 57 AND iterator < len DO

         SET iterator = iterator + 1;

      END WHILE;

   END WHILE;

   RETURN output;

END;;

在测试服务器上测试5000次：

-- original

Execution Time : 7.389 sec

Execution Time : 7.257 sec

Execution Time : 7.506 sec

-- ORD between not string IN

Execution Time : 4.031 sec

-- With less substrings

Execution Time : 3.243 sec

Execution Time : 3.415 sec

Execution Time : 2.848 sec