CODEWITHSUNDEEP
phpmysql
Sygon
Sygon

Reputation: 222

PHP MYSQL search for postcode using LIKE

I have a query which searches for postcode using LIKE. Now the postcode is fine if someone puts the full postcode e.g. SW14 4NP. But if someone was to put in SW1 it would return anything with SW1+ results. How can I make it so that if someone put SW1, it would display only SW1 postcode and not SW11, SW14 etc.

My query is:

SELECT postcode FROM data WHERE postcode LIKE '$search%'

The database for postcodes are stored as SW14 4NP etc with a space.

Upvotes: 0

Views: 480

Answers (4)

eggyal
eggyal

Reputation: 125835

Use regular expression based pattern matching:

WHERE postcode RLIKE '^$search( ?[0-9][A-Z]{2})?'

Upvotes: 2

Aris
Aris

Reputation: 5057

    SELECT postcode FROM data WHERE postcode LIKE '$search %'

But say the user inserts only SW, it will return nothing, while with your current query it will return anything starting with SW. Depends what you want to do.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425251

SELECT  postcode
FROM    data
WHERE   postcode LIKE 'SW1 %'

Upvotes: 1

nvanesch
nvanesch

Reputation: 2600

SELECT postcode FROM data WHERE postcode LIKE '$search %'

by simply adding the space after the search key. then it will find all SW1 codes (or whatever you look for) but not the SW11

Upvotes: 3

Related Questions