2

I have a table structured as follows:

address_number  address_street  address_town  address_county  address_postcode

There is a search box on the site where people can enter a partial address and I need to work out how to return accurate results based on the information they submitted, BUT there are a few complications the post code is stored like this: BN2 1HN and the address_number can be as small as just 1 character.

I have tried to CONCAT the address fields together and compare that to the input but none of it is working, I also tried going through each of the fields in the table with a LIKE Loop but that was not satisfactory at all. Ideally I would like the most accurate results returned first but I appreciate this can only really be done with MATCH AGAINST.

Any help would be greatly appreciated and a definitive answer will get you a drink if you have a PayPal Donate button (Providing etiquette allows for this?). Its my first question as I am stubborn and normally like to puzzle this stuff out for myself.

Matt
  • 22,721
  • 17
  • 71
  • 112
Christopher Diplock
  • 21
  • 1
  • 2
    Consider adding wich RDBMS are you using, include version if necessary. Also if you have some working query post it too, it's a point to start from. – Yaroslav Sep 21 '12 at 21:50

1 Answers1

0

One may guess you're using MySQL due to the mention of

MATCH(col1,col2) ... AGAINST(expr)

but here is an approach using LIKE, which may help in SQL Server:

/*
CREATE TABLE tab1 (address_number VARCHAR(10),  address_street VARCHAR(50), 
address_town VARCHAR(50),  address_county VARCHAR(50),  address_postcode VARCHAR(50))
INSERT INTO tab1 VALUES ('100','Queen Street','Winchester','Hartford','XYZ 123')
INSERT INTO tab1 VALUES ('10','Downing Street','London','','XYZ 124')
--*/
SELECT * FROM Tab1
WHERE (address_number+' '+address_street+' '+address_town+' '+address_county
+' '+address_postcode + ' '+replace(address_postcode,' ','')/*remove blank*/
) LIKE '%100 QUEEN%'

To be google-like, you'd need to try lots of variations on what the user entered, and order them by whether the address starts with what the user entered, contains it literally, etc.

Kip Bryan
  • 461
  • 4
  • 6