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.

Answers


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.


Need Your Help

Fetch file from particular date

unix awk grep unix-timestamp ls

New to Unix and I'm trying to fetch files from a directory having current date.

onDestroy or onPause not called in a TabHosts child activity

android android-tabhost

I have a Tabhost and in that tabhost there are 4 tabs and in every tab there is an activity. When an activity lose focus i want to catch it with onDestroy or onPause method.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.