MySQL strip non-numeric characters to compare
MysqlRegexMysql Problem Overview
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.
I imagine something like this would work, if PHP functions worked in MySQL.
SELECT * FROM foo WHERE preg_replace("/[^0-9]/","",bar) = '12345'
What's the equivalent function or way to do this with just MySQL?
Speed is not important.
Mysql Solutions
Solution 1 - Mysql
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
$$
Solution 2 - Mysql
Most upvoted answer above isn'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:
-- 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
Solution 3 - Mysql
You can easily do what you want with REGEXP_REPLACE (compatible with MySQL 8+ and 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.
>
> Go to REGEXP_REPLACE doc: MySQL or MariaDB
Try it:
SELECT REGEXP_REPLACE('123asd12333', '[a-zA-Z]+', '');
Output:
12312333
Solution 4 - Mysql
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.
Solution 5 - Mysql
There's no regexp replace, only a plain string 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:
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.
Solution 6 - Mysql
The simplest way I can think to do it is to use the MySQL REGEXP operator 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.
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.
Solution 7 - Mysql
On MySQL 8.0+ there is a new native function called REGEXP_REPLACE. A clean solution to this question would be:
SELECT * FROM foo WHERE REGEXP_REPLACE(bar,'[^0-9]+',"") = '12345'
Solution 8 - Mysql
This blog post details how to strip non-numeric characters from a string via a MySQL function:
SELECT NumericOnly("asdf11asf");
returns 11
http://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/
Solution 9 - Mysql
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
Also I found this place that could help.
Solution 10 - Mysql
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.
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.
You'd need to do a one time scan of the table to create all the reference_number fields for existing products.
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.
Solution 11 - Mysql
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.
phpMyAdmin version is 4.1.14; MySQL version 5.6.20
I also added a length limiter using
DECLARE count INT DEFAULT 0; in the declarations
AND count < 5 in the WHILE statement
SET COUNT=COUNT+1; in the IF statement
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
Solution 12 - Mysql
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.
Of course, if the table is too big, this won't work well.
Solution 13 - Mysql
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:
col1
(513)983-3983
1-838-338-9898
phone983-889-8383
select remove_alpha(col1) from mytable
The result would be;
5139833983
18383389898
9838898383
Solution 14 - Mysql
thought I would share this since I built it off the function from here. I rearranged just so I can read it easier (I'm just server side).
You call it by passing in a table name and column name to have it strip all existing non-numeric characters from that column. I inherited a lot of bad table structures that put a ton of int fields as varchar so I needed a way to clean these up quickly before I can modify the column to an integer.
drop procedure if exists strip_non_numeric_characters;
DELIMITER ;;
CREATE PROCEDURE `strip_non_numeric_characters`(
tablename varchar(100)
,columnname varchar(100)
)
BEGIN
-- =============================================
-- Author: <Author,,David Melton>
-- Create date: <Create Date,,2/26/2019>
-- Description: <Description,,loops through data and strips out the bad characters in whatever table and column you pass it>
-- =============================================
#this idea was generated from the idea STRIP_NON_DIGIT function
#https://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare
declare input,output varchar(255);
declare iterator,lastDigit,len,counter int;
declare date_updated varchar(100);
select column_name
into date_updated
from information_schema.columns
where table_schema = database()
and extra rlike 'on update CURRENT_TIMESTAMP'
and table_name = tablename
limit 1;
#only goes up to 255 so people don't run this for a longtext field
#just to be careful, i've excluded columns that are part of keys, that could potentially mess something else up
set @find_column_length =
concat("select character_maximum_length
into @len
from information_schema.columns
where table_schema = '",database(),"'
and column_name = '",columnname,"'
and table_name = '",tablename,"'
and length(ifnull(character_maximum_length,100)) < 255
and data_type in ('char','varchar')
and column_key = '';");
prepare stmt from @find_column_length;
execute stmt;
deallocate prepare stmt;
set counter = 1;
set len = @len;
while counter <= ifnull(len,1) DO
#this just removes it by putting all the characters before and after the character i'm looking at
#you have to start at the end of the field otherwise the lengths don't stay in order and you have to run it multiple times
set @update_query =
concat("update `",tablename,"`
set `",columnname,"` = concat(substring(`",columnname,"`,1,",len - counter,"),SUBSTRING(`",columnname,"`,",len - counter,",",counter - 1,"))
",if(date_updated is not null,concat(",`",date_updated,"` = `",date_updated,"`
"),''),
"where SUBSTRING(`",columnname,"`,",len - counter,", 1) not REGEXP '^[0-9]+$';");
prepare stmt from @update_query;
execute stmt;
deallocate prepare stmt;
set counter = counter + 1;
end while;
END ;;
DELIMITER ;
Solution 15 - Mysql
To search for numbers that match a particular numeric pattern in a string, first remove all the alphabets and special characters in a similar manner as below then convert the value to an integer and then search
SELECT *
FROM foo
WHERE Convert(Regexp_replace(bar, '[a-zA-Z]+', ''), signed) = 12345