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?

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

| improve this answer | |
  • Thanks Akina, this works as expected. – Jaimin Sutariya Oct 13 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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