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))&nbsp; &nbsp;RETURNS VARCHAR(255)BEGIN&nbsp; &nbsp;DECLARE output&nbsp; &nbsp;VARCHAR(255) DEFAULT '';&nbsp; &nbsp;DECLARE iterator INT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DEFAULT 1;&nbsp; &nbsp;WHILE iterator < (LENGTH(input) + 1) DO&nbsp; &nbsp; &nbsp; IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET output = CONCAT(output, SUBSTRING(input, iterator, 1));&nbsp; &nbsp; &nbsp; END IF;&nbsp; &nbsp; &nbsp; SET iterator = iterator + 1;&nbsp; &nbsp;END WHILE;&nbsp; &nbsp;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

获得最高评价的答案（@ user1467716）并不是最快的。为他们提出反弹的工作建议而向他们致以崇高的敬意！这是一个改进的版本：DELIMITER ;;DROP FUNCTION IF EXISTS `STRIP_NON_DIGIT`;;CREATE DEFINER=`root`@`localhost` FUNCTION `STRIP_NON_DIGIT`(input VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8READS SQL DATABEGIN&nbsp; &nbsp;DECLARE output&nbsp; &nbsp; VARCHAR(255) DEFAULT '';&nbsp; &nbsp;DECLARE iterator&nbsp; INT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DEFAULT 1;&nbsp; &nbsp;DECLARE lastDigit INT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DEFAULT 1;&nbsp; &nbsp;DECLARE len&nbsp; &nbsp; &nbsp; &nbsp;INT;&nbsp; &nbsp;SET len = LENGTH(input) + 1;&nbsp; &nbsp;WHILE iterator < len DO&nbsp; &nbsp; &nbsp; -- skip past all digits&nbsp; &nbsp; &nbsp; SET lastDigit = iterator;&nbsp; &nbsp; &nbsp; WHILE ORD(SUBSTRING(input, iterator, 1)) BETWEEN 48 AND 57 AND iterator < len DO&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET iterator = iterator + 1;&nbsp; &nbsp; &nbsp; END WHILE;&nbsp; &nbsp; &nbsp; IF iterator != lastDigit THEN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET output = CONCAT(output, SUBSTRING(input, lastDigit, iterator - lastDigit));&nbsp; &nbsp; &nbsp; END IF;&nbsp; &nbsp; &nbsp; WHILE ORD(SUBSTRING(input, iterator, 1)) NOT BETWEEN 48 AND 57 AND iterator < len DO&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET iterator = iterator + 1;&nbsp; &nbsp; &nbsp; END WHILE;&nbsp; &nbsp;END WHILE;&nbsp; &nbsp;RETURN output;END;;在测试服务器上测试5000次：-- originalExecution Time : 7.389 secExecution Time : 7.257 secExecution Time : 7.506 sec-- ORD between not string INExecution Time : 4.031 sec-- With less substringsExecution Time : 3.243 secExecution Time : 3.415 secExecution Time : 2.848 sec