Against Match failing on multiple address fields, I can't work it out
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.
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.