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的等价函数是什么?
12 个解决方案
#1
32
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
$$
#2
7
There's no regexp replace, only a plain string REPLACE().
没有regexp替换，只有一个普通的字符串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操作符，但它只是一个match tester而不是一个replacer，所以必须将逻辑从内往外:
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.
这就像你的版本一样，但更准确地匹配。两者的性能都很差，需要没有索引的全表扫描。
#3
6
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.
我仍然希望找到一个类似于原始问题中的项目的更好的解决方案。
#4
4
Most upvoted answer (@user1467716) isn't the fastest. Full kudos to them for giving a working proposal to bounce off!
大多数向上投票的答案(@user1467716)不是最快的。非常感谢他们给了我一个工作建议，让我跳下来!
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
#5
3
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_replace function 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.
就我个人而言，如果这个纯数字数据如此重要，我将保留一个单独的字段来包含剥离的数据。它将使您的查找比使用正则表达式搜索快得多。
#6
3
This blog post details how to strip non-numeric characters from a string via a MySQL function:
这篇博文详细介绍了如何通过MySQL函数从字符串中删除非数字字符:
SELECT NumericOnly("asdf11asf");
选择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/
#7
1
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时需要在DB中找到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.
最后，我向product表添加了一个新字段reference_number，并让php去掉product_number中的none alpha数值，以便每当添加新产品时填充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.
然后，您可以设置索引，即使速度不是这个操作的因素，保持数据库良好运行仍然是一个好主意，这样该查询就不会使它陷入停顿，并降低其他查询的速度。
#8
1
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
声明count INT默认为0;在声明
AND count < 5 in the WHILE statement
在WHILE语句中计数为< 5
SET COUNT=COUNT+1; in the IF statement
组数=计数+ 1;如果语句中
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
#9
1
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]]])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.
将匹配模式pat指定的正则表达式的字符串expr中的出现替换为替换字符串repl，并返回结果字符串。如果expr、pat或repl为NULL，则返回值为NULL。
Go to REGEXP_REPLACE doc: MySQL or MariaDB
转到REGEXP_REPLACE doc: MySQL或MariaDB
Try it:
试一试:
SELECT REGEXP_REPLACE('123asd12333', '[a-zA-Z]+', '');
Output:
输出:
12312333
#10
0
There's no regex replace as far as I'm concerned, but I found this solution;
就我而言，没有regex替换，但我找到了这个解决方案;
--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 place that could help.
我也找到了这个可以帮助你的地方。
#11
0
How big is table with foo? If it is small, and speed really doesn't matter, you might pull the row ID and foo, loop over it using the PHP replace functions to compare, and then pull the info you want by row number.
有foo的桌子有多大?如果它很小，而且速度真的不重要，那么您可以拉出行ID和foo，使用PHP replace函数对其进行循环以进行比较，然后按行号拉出所需的信息。
Of course, if the table is too big, this won't work well.
当然，如果桌子太大，这就不好用了。
#12
0
try this example. this is used for phone numbers, however you can modify it for your needs.
试试这个例子。这是用于电话号码，但是您可以根据需要修改它。
-- function removes non numberic characters from input
-- returne only the numbers in the string
CREATE DEFINER =`root`@`localhost` FUNCTION `remove_alpha`(inputPhoneNumber VARCHAR(50))
RETURNS VARCHAR(50)
CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE inputLenght INT DEFAULT 0;
-- var for our iteration
DECLARE counter INT DEFAULT 1;
-- if null is passed, we still return an tempty string
DECLARE sanitizedText VARCHAR(50) DEFAULT '';
-- holder of each character during the iteration
DECLARE oneChar VARCHAR(1) DEFAULT '';
-- we'll process only if it is not null.
IF NOT ISNULL(inputPhoneNumber)
THEN
SET inputLenght = LENGTH(inputPhoneNumber);
WHILE counter <= inputLenght DO
SET oneChar = SUBSTRING(inputPhoneNumber, counter, 1);
IF (oneChar REGEXP ('^[0-9]+$'))
THEN
SET sanitizedText = Concat(sanitizedText, oneChar);
END IF;
SET counter = counter + 1;
END WHILE;
END IF;
RETURN sanitizedText;
END
to use this user defined function (UDF). let's say you have a column of phone numbers:
要使用此用户定义函数(UDF)。假设你有一列电话号码:
col1
(513)983-3983
1-838-338-9898
phone983-889-8383
select remove_alpha(col1) from mytable
The result would be;
其结果将是;
5139833983
18383389898
9838898383
#1
32
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
$$
#2
7
There's no regexp replace, only a plain string REPLACE().
没有regexp替换，只有一个普通的字符串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操作符，但它只是一个match tester而不是一个replacer，所以必须将逻辑从内往外:
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.
这就像你的版本一样，但更准确地匹配。两者的性能都很差，需要没有索引的全表扫描。
#3
6
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.
我仍然希望找到一个类似于原始问题中的项目的更好的解决方案。
#4
4
Most upvoted answer (@user1467716) isn't the fastest. Full kudos to them for giving a working proposal to bounce off!
大多数向上投票的答案(@user1467716)不是最快的。非常感谢他们给了我一个工作建议，让我跳下来!
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
#5
3
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_replace function 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.
就我个人而言，如果这个纯数字数据如此重要，我将保留一个单独的字段来包含剥离的数据。它将使您的查找比使用正则表达式搜索快得多。
#6
3
This blog post details how to strip non-numeric characters from a string via a MySQL function:
这篇博文详细介绍了如何通过MySQL函数从字符串中删除非数字字符:
SELECT NumericOnly("asdf11asf");
选择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/
#7
1
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时需要在DB中找到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.
最后，我向product表添加了一个新字段reference_number，并让php去掉product_number中的none alpha数值，以便每当添加新产品时填充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.
然后，您可以设置索引，即使速度不是这个操作的因素，保持数据库良好运行仍然是一个好主意，这样该查询就不会使它陷入停顿，并降低其他查询的速度。
#8
1
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
声明count INT默认为0;在声明
AND count < 5 in the WHILE statement
在WHILE语句中计数为< 5
SET COUNT=COUNT+1; in the IF statement
组数=计数+ 1;如果语句中
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
#9
1
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]]])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.
将匹配模式pat指定的正则表达式的字符串expr中的出现替换为替换字符串repl，并返回结果字符串。如果expr、pat或repl为NULL，则返回值为NULL。
Go to REGEXP_REPLACE doc: MySQL or MariaDB
转到REGEXP_REPLACE doc: MySQL或MariaDB
Try it:
试一试:
SELECT REGEXP_REPLACE('123asd12333', '[a-zA-Z]+', '');
Output:
输出:
12312333
#10
0
There's no regex replace as far as I'm concerned, but I found this solution;
就我而言，没有regex替换，但我找到了这个解决方案;
--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 place that could help.
我也找到了这个可以帮助你的地方。
#11
0
How big is table with foo? If it is small, and speed really doesn't matter, you might pull the row ID and foo, loop over it using the PHP replace functions to compare, and then pull the info you want by row number.
有foo的桌子有多大?如果它很小，而且速度真的不重要，那么您可以拉出行ID和foo，使用PHP replace函数对其进行循环以进行比较，然后按行号拉出所需的信息。
Of course, if the table is too big, this won't work well.
当然，如果桌子太大，这就不好用了。
#12
0
try this example. this is used for phone numbers, however you can modify it for your needs.
试试这个例子。这是用于电话号码，但是您可以根据需要修改它。
-- function removes non numberic characters from input
-- returne only the numbers in the string
CREATE DEFINER =`root`@`localhost` FUNCTION `remove_alpha`(inputPhoneNumber VARCHAR(50))
RETURNS VARCHAR(50)
CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE inputLenght INT DEFAULT 0;
-- var for our iteration
DECLARE counter INT DEFAULT 1;
-- if null is passed, we still return an tempty string
DECLARE sanitizedText VARCHAR(50) DEFAULT '';
-- holder of each character during the iteration
DECLARE oneChar VARCHAR(1) DEFAULT '';
-- we'll process only if it is not null.
IF NOT ISNULL(inputPhoneNumber)
THEN
SET inputLenght = LENGTH(inputPhoneNumber);
WHILE counter <= inputLenght DO
SET oneChar = SUBSTRING(inputPhoneNumber, counter, 1);
IF (oneChar REGEXP ('^[0-9]+$'))
THEN
SET sanitizedText = Concat(sanitizedText, oneChar);
END IF;
SET counter = counter + 1;
END WHILE;
END IF;
RETURN sanitizedText;
END
to use this user defined function (UDF). let's say you have a column of phone numbers:
要使用此用户定义函数(UDF)。假设你有一列电话号码:
col1
(513)983-3983
1-838-338-9898
phone983-889-8383
select remove_alpha(col1) from mytable
The result would be;
其结果将是;
5139833983
18383389898
9838898383