MySQL matching partial strings with LIKE

Question

I'm trying create a MySQL query which will return a row (or more, but I'm only interested in a single row) an area suggestion when provided with a postcode.

So, I have a table like so

area_id | postcode_start
1       | NE
2       | DL
3       | DL1
4       | DL2
5       | DL6
...

And I wish to provide a postcode, for example DL5 8TB and for it to return the row 1 | DL as the first characters match.

Another example DL6 4GH would return two rows, 5 | DL6 and 2 | DL

SELECT *
    FROM (`area_postcodes`)
    WHERE CONCAT(LOWER(`area_postcodes`.`postcode_start`),'%')
        LIKE 'dl5 8tb';

Apparently it's a valid query but it always returns an empty set.

You'll see I've added the wildcard % to the other side of the query but it doesn't seem to work and I don't know where else to even consider guessing how to go about it, it's almost like the reverse of a normal MySQL LIKE query.

Is there something magical with prefixes with length 2 (i.e. why wouldn't DL6 4GH and DL5 8TB both match all of area_id 2,3,4,5? -- all of which have D as a prefix) — thebjorn
– thebjorn, Commented Jul 29, 2014 at 14:11
No, I'd like all the characters under postcode start to match, so DL5 4UH would only match DL but DL6 7HG would match both DL and DL6 — Sam
– Sam, Commented Jul 29, 2014 at 14:14

VMai · Accepted Answer · 2014-07-29 14:14:49Z

5

You were very close:

SELECT 
    * 
FROM 
    area_postcodes
WHERE
    'dl5 8tb' LIKE CONCAT(postcode_start, '%');

answered Jul 29, 2014 at 14:14

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

This seems to be working, thanks so much. Who'd have thought you could put the column on the right //fffffff

Collectives™ on Stack Overflow

MySQL matching partial strings with LIKE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related