MySQL MySQL剥离非数字字符进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/287105/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL strip non-numeric characters to compare
提问by Chris Bartow
I'm looking to find records in a table that match a specific number that the user enters. So, the user may enter 12345, but this could be 123zz4-5 in the database.
我希望在表中查找与用户输入的特定数字相匹配的记录。因此，用户可能会输入 12345，但这可能是数据库中的 123zz4-5。
I imagine something like this would work, if PHP functions worked in MySQL.
如果 PHP 函数在 MySQL 中工作，我想这样的事情会起作用。
SELECT * FROM foo WHERE preg_replace("/[^0-9]/","",bar) = '12345'
What's the equivalent function or way to do this with just MySQL?
仅使用 MySQL 执行此操作的等效功能或方法是什么？
采纳答案by Chris Bartow
While it's not pretty and it shows results that don't match, this helps:
虽然它不漂亮并且显示的结果不匹配，但这有助于：
SELECT * FROM foo WHERE bar LIKE = '%1%2%3%4%5%'
I would still like to find a better solution similar to the item in the original question.
我仍然想找到一个更好的解决方案，类似于原始问题中的项目。
回答by user1467716
I realise that this is an ancient topic but upon googling this problem I couldn't find a simple solution (I saw the venerable agents but think this is a simpler solution) so here's a function I wrote, seems to work quite well.
我意识到这是一个古老的话题，但是在谷歌搜索这个问题时我找不到一个简单的解决方案（我看到了古老的代理，但认为这是一个更简单的解决方案）所以这是我写的一个函数，似乎工作得很好。
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
$$
回答by wally
Most upvoted answer aboveisn't the fastest.
Full kudos to them for giving a working proposal to bounce off!
上面最受好评的答案并不是最快的。
非常感谢他们提出了一个可行的建议来反弹！
This is an improved version:
这是一个改进版本：
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;;
Testing 5000 times on a test server:
在测试服务器上测试 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
回答by Marlom
You can easily do what you want with REGEXP_REPLACE(compatible with MySQL 8+ and MariaDB 10.0.5+)
您可以轻松地做您想做的事REGEXP_REPLACE（兼容 MySQL 8+ 和 MariaDB 10.0.5+）
REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.
REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])用替换字符串 repl 替换字符串 expr 中与模式 pat 指定的正则表达式匹配的匹配项，并返回结果字符串。如果 expr、pat 或 repl 为 NULL，则返回值为 NULL。
Try it:
尝试一下：
SELECT REGEXP_REPLACE('123asd12333', '[a-zA-Z]+', '');
Output:
输出：
12312333
回答by bobince
There's no regexp replace, only a plain string REPLACE().
没有正则表达式替换，只有一个普通字符串 REPLACE()。
MySQL has the REGEXP operator, but it's only a match tester not a replacer, so you would have to turn the logic inside-out:
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]*';
This is like your version with LIKE but matches more accurately. Both will perform equally badly, needing a full table scan without indexes.
这就像你的 LIKE 版本，但匹配得更准确。两者的性能都一样差，需要无索引的全表扫描。
回答by Gareth
The simplest way I can think to do it is to use the MySQL REGEXP operator a la:
我能想到的最简单的方法是使用 MySQL REGEXP 运算符 a la：
WHERE foo LIKE '1\D*2\D*3\D*4\D*5'
It's not especially pretty but MySQL doesn't have a preg_replacefunction so I think it's the best you're going to get.
它不是特别漂亮，但 MySQL 没有preg_replace功能，所以我认为它是你将要得到的最好的。
Personally, if this only-numeric data is so important, I'd keep a separate field just to contain the stripped data. It'll make your lookups a lot faster than with the regular expression search.
就个人而言，如果这个只有数字的数据如此重要，我会保留一个单独的字段来包含剥离的数据。它将使您的查找比使用正则表达式搜索快得多。
回答by Jeremy Warne
This blog post details how to strip non-numeric characters from a string via a MySQL function:
这篇博文详细介绍了如何通过 MySQL 函数从字符串中去除非数字字符：
SELECT NumericOnly("asdf11asf");
SELECT NumericOnly("asdf11asf");
returns 11
返回 11
http://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/
http://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/
回答by user396149
I have a similar situation, matching products to barcodes where the barcode doesn't store none alpha numerics sometimes, so 102.2234 in the DB needs to be found when searching for 1022234.
我有类似的情况，将产品与条形码匹配，有时条形码不存储任何字母数字，因此在搜索 1022234 时需要在数据库中找到 102.2234。
In the end I just added a new field, reference_number to the products tables, and have php strip out the none alpha numerics in the product_number to populate reference_number whenever a new products is added.
最后，我只是在产品表中添加了一个新字段 reference_number，并让 php 去掉 product_number 中的无字母数字，以便在添加新产品时填充 reference_number。
You'd need to do a one time scan of the table to create all the reference_number fields for existing products.
您需要对表进行一次扫描，以便为现有产品创建所有 reference_number 字段。
You can then setup your index, even if speed is not a factor for this operation, it is still a good idea to keep the database running well so this query doesn't bog it down and slow down other queries.
然后您可以设置您的索引，即使速度不是这个操作的一个因素，保持数据库运行良好仍然是一个好主意，这样这个查询就不会陷入困境并减慢其他查询的速度。
回答by modle13
I came across this solution. The top answer by user1467716 will work in phpMyAdmin with a small change: add a second delimiter tag to the end of the code.
我遇到了这个解决方案。user1467716 的最佳答案将在 phpMyAdmin 中工作，只需稍作更改：在代码末尾添加第二个分隔符标记。
phpMyAdmin version is 4.1.14; MySQL version 5.6.20
phpMyAdmin 版本为 4.1.14；MySQL 版本 5.6.20
I also added a length limiter using
我还使用添加了一个长度限制器
DECLARE count INT DEFAULT 0;in the declarations
DECLARE count INT DEFAULT 0;在声明中
AND count < 5in the WHILEstatement
AND count < 5在WHILE声明中
SET COUNT=COUNT+1;in the IFstatement
SET COUNT=COUNT+1;在IF声明中
Final form:
最终形式：
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;
DECLARE count INT DEFAULT 0;
WHILE iterator < (LENGTH(input) + 1) AND count < 5 DO --limits to 5 chars
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));
SET COUNT=COUNT+1;
END IF;
SET iterator = iterator + 1;
END WHILE;
RETURN output;
END
$$
DELIMITER $$ --added this
回答by Nelson Miranda
There's no regex replace as far as I'm concerned, but I found this solution;
就我而言，没有正则表达式替换，但我找到了这个解决方案；
--Create a table with numbers
DROP TABLE IF EXISTS ints;
CREATE TABLE ints (i INT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO ints (i) VALUES
( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
--Then extract the numbers from the specified column
SELECT
bar,
GROUP_CONCAT(SUBSTRING(bar, i, 1) ORDER BY i SEPARATOR '')
FROM foo
JOIN ints ON i BETWEEN 1 AND LENGTH(bar)
WHERE
SUBSTRING(bar, i, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
GROUP BY bar;
It works for me and I use MySQL 5.0
它对我有用，我使用 MySQL 5.0
Also I found this placethat could help.
我也发现这个地方可以提供帮助。
