MySQL: How to match data with regular expression string

Question

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?

• Dave Stokes: Have you tried REGEXP, SELECT 'RD489' REGEXP 'RD4*'; ?
• Jaimin Sutariya: Thanks @DaveStokes, Let me try it.
• Akina: SELECT 'RD489' LIKE 'RD4%'; or SELECT 'RD489' REGEXP '^RD4.*'; LIKE, REGEXP
• Jaimin Sutariya: Thanks @Akina, Sorry but like does not work for me as I may have RD4* or ABC4* stored.
• Akina: I understand that ABC4* / RDTU22* are stored in a table. But where are ABC41 and RDTU225? they are parameters which are transferred into prepared query?

Answer 1

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).

• Jaimin Sutariya: Thanks Akina, this works as expected.