MySQL strip non-numeric characters to compare

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

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.

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.

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'

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.

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.

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.

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 ;

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 

I think you don't need complicated functions for that.

I've found a REGEXP_REPLACE solution using built-in mysql character class names. You can read about them in a table in the docs. Basically, they are mysql-specific names for commonly matched groups of characters like [:alnum:] for alpha-numeric characters, [:alpha:] for only alphabetic characters and so on.

So my version of the REGEXP_REPLACE:

 REGEXP_REPLACE('My number is: +59 (29) 889-23-56', '[[:alpha:][:blank:][:punct:][:cntrl:]]', '')

will yield 59298892356 as per requirements.