MySQL: How to match data with regular expression string

MySQLpattern matching

I have a postcode column in my table with values like AB45*, RD4*, ADB567 etc.

Now I want to match my data with this column. The * means that a string starting with AB45 should match with this column. If I use AB45 45D it should fetch a row containing AB45*

If I use RD489 on my field, it should match with RD4* column value.

What can I use to achieve this requirement?

Best Answer

CREATE TABLE patterns (pattern VARCHAR(16));
INSERT INTO patterns VALUES ('ABC4*'), ('RDTU22*');
SELECT * FROM patterns;

| pattern |
| :------ |
| ABC4*   |
| RDTU22* |

CREATE TABLE values_to_check (val VARCHAR(255));
INSERT INTO values_to_check VALUES ('ABC41'), ('ABC49'), ('RDTU225'), ('RDTU229');
SELECT * FROM values_to_check;

| val     |
| :------ |
| ABC41   |
| ABC49   |
| RDTU225 |
| RDTU229 |

SELECT values_to_check.val, patterns.pattern
FROM values_to_check
JOIN patterns ON values_to_check.val LIKE REPLACE(patterns.pattern, '*', '%');

val     | pattern
:------ | :------
ABC41   | ABC4*  
ABC49   | ABC4*  
RDTU225 | RDTU22*
RDTU229 | RDTU22*

SELECT values_to_check.val, patterns.pattern, 
       values_to_check.val LIKE REPLACE(patterns.pattern, '*', '%') does_they_match
FROM values_to_check
JOIN patterns;

val     | pattern | does_they_match
:------ | :------ | --------------:
ABC41   | ABC4*   |               1
ABC41   | RDTU22* |               0
ABC49   | ABC4*   |               1
ABC49   | RDTU22* |               0
RDTU225 | ABC4*   |               0
RDTU225 | RDTU22* |               1
RDTU229 | ABC4*   |               0
RDTU229 | RDTU22* |               1

db<>fiddle here

PS. REPLACE() converts the pattern to the format clear for LIKE (replaces the asterisk with the percent sign).

Related Question