CODEWITHSUNDEEP
mysqlsqlsql-like
Sam
Sam

Reputation: 2821

MySQL matching partial strings with LIKE

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.

Upvotes: 2

Views: 4260

Answers (1)

VMai
VMai

Reputation: 10346

You were very close:

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

Upvotes: 5

Related Questions