0

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?

10
  • 1
    Have you tried REGEXP, SELECT 'RD489' REGEXP 'RD4*'; ? Oct 12, 2020 at 13:33
  • Thanks @DaveStokes, Let me try it. Oct 12, 2020 at 13:37
  • SELECT 'RD489' LIKE 'RD4%'; or SELECT 'RD489' REGEXP '^RD4.*'; LIKE, REGEXP
    – Akina
    Oct 12, 2020 at 13:43
  • Thanks @Akina, Sorry but like does not work for me as I may have RD4* or ABC4* stored. Oct 12, 2020 at 13:48
  • 1
    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?
    – Akina
    Oct 12, 2020 at 17:01

1 Answer 1

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

1
  • Thanks Akina, this works as expected. Oct 13, 2020 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.