MySQL 'LIKE' query with spaces

Question

I have a MySQL database with postcodes in it, sometimes in the database there are spaces in the postcodes (eg. NG1 1AB) sometimes there are not (eg. NG11AB). Simlarly in the PHP query to read from the database the person searching the database may add a space or not. I've tried various different formats using LIKE but can't seem to find an effective means of searching so that either end it would bring up the same corresponding row (eg. searching for either NG11AB or NG1 1AB to bring up 'Bob Smith' or whatever the corresponding row field would be).

Any suggestions?

Answer 1

I wouldn't even bother with LIKE or regex and simply remove spaces and compare the strings:

SELECT *
FROM mytable
WHERE LOWER(REPLACE(post_code_field, ' ', '')) = LOWER(REPLACE(?, ' ', ''))

Note that I also convert both values to lower case to still match correctly if the user enters a lower-case post code.

Answer 2

SELECT *
FROM MYTABLE
WHERE REPLACE(MYTABLE.POSTCODE, ' ', '') LIKE '%input%'

Make sure your PHP input is trimmed as well