5

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?

Improve this question
1
  • Show us what you've tried, please. Commented Aug 30, 2013 at 8:55

4 Answers 4

Reset to default
12

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.

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

6 Comments

Would the query also work if the person searched in uppercase too?
Yes, it would. This query converts both values to lower case and then compares them.
Fantastic! Thanks! I shall implement this now :)
sorry if i am wrong, just need a clarification here. Is mysql search case insensitive?? do we need the function lower() here?
@Mani By default, it is not case-insensitive and you do need conversion. You can change your collation - but why?
|
4 
SELECT *
FROM MYTABLE
WHERE REPLACE(MYTABLE.POSTCODE, ' ', '') LIKE '%input%'

Make sure your PHP input is trimmed as well

Improve this answer

1 Comment

Cheers, have used this bit in another part of the PHP script - very concise.
0

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
Improve this answer

Comments

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

See also this question.

Improve this answer

2 Comments

The link is broken
Fixed, @Pathros. The original example is accessible here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.