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?

Show us what you've tried, please. – Barmar Aug 30 '13 at 08:55 — Barmar, Aug 30 '13 at 08:55

score 11 · Accepted Answer · answered Aug 30 '13 at 08:57

11

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.

answered Aug 30 '13 at 08:57

Aleks G

56,435
29
168
265

Would the query also work if the person searched in uppercase too? – Elliot Reed Aug 30 '13 at 09:02
Yes, it would. This query converts both values to lower case and then compares them. – Aleks G Aug 30 '13 at 09:02
Fantastic! Thanks! I shall implement this now :) – Elliot Reed Aug 30 '13 at 09:05
sorry if i am wrong, just need a clarification here. Is mysql search case insensitive?? do we need the function lower() here? – DB_learner Aug 30 '13 at 09:15
1

@Mani By default, it is **not** case-insensitive and you do need conversion. You can change your collation - but why? – Aleks G Aug 30 '13 at 09:19
@Aleks. I thought default was case-insensitive. Thanks for the information. – DB_learner Aug 30 '13 at 09:22

score 3 · Answer 2 · answered Aug 30 '13 at 08:56

3

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

Make sure your PHP input is trimmed as well

answered Aug 30 '13 at 08:56

Anonymoose

2,389
6
36
69

Cheers, have used this bit in another part of the PHP script - very concise. – Elliot Reed Aug 30 '13 at 09:25

score 0 · Answer 3 · answered Aug 30 '13 at 08:56

Assuming your column has more than just the postcode in it: Remove all spaces before doing the like test:

where replace(postcode, ' ', '') like '%NG11AB%'

If your columns has just the postcode in it:

where replace(postcode, ' ', '') = 'NG11AB'

or

where postcode like 'N%G%1%1%A%B'

Regex will work for both cases. The following regex allows optional spaces between each letter:

where postcode rlike 'N ?G ?1 ?1 ?A ?B' -- use regex

Paolo Gibellini · Answer 4 · 2023-03-28T06:50:22.260

0

You can use a regular expression with * metacharacter, which matches 0 or more chars.

For example:

SELECT * FROM the_table
WHERE postcode REGEXP "NG1*1AB"

matches NG11AB and NG1 1AB

A list of the most common metacharacters:

* match zero or more
+ match one or more
{n} match n times
{m,n} match m through n times
{n,} match n or more times
^ beginning of line
$ end of line
[[:<:]] match beginning of words
[[:>:]] match ending of words
[:class:] match a character class:
  [:alpha:] for letters
  [:space:] for whitespace
  [:punct:] for punctuation
  [:upper:] for upper case letters
[abc] match one of enclosed chars
[^xyz] match any char not enclosed
| separates alternatives

MySQL 'LIKE' query with spaces

4 Answers4