CODEWITHSUNDEEP
mysqlsql
Elliot Reed
Elliot Reed

Reputation: 749

MySQL 'LIKE' query with spaces

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?

Upvotes: 5

Views: 21381

Answers (4)

Paolo Gibellini
Paolo Gibellini

Reputation: 320

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

See also this question.

Upvotes: 0

Aleks G
Aleks G

Reputation: 57306

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.

Upvotes: 12

Anonymoose
Anonymoose

Reputation: 2461

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

Make sure your PHP input is trimmed as well

Upvotes: 4

Bohemian
Bohemian

Reputation: 424983

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

Upvotes: 0

Related Questions