sql - Query to get a city by a truckload of possible parameters -

while testing search engine, realised not pretty awfull, didn't work lot of combinations user input.

i have search field user can input wants. region, city name, zipcode or country or combinations of in no predetermined order or predetermined separator.

this quite difficult find cities correspond user wants because of big number of possible combinations user enter in field.

what more difficult lot of cities have article in name or composed name, article or words user or couldn't enter. example, since i'm french, i'll use example of city called: la rochelle.

the user search la rochelle, rochelle or la-rochelle city's name. if wants more precise, add zip-code after, or before, or country or both...

i can't find how optimize search city random user input. there conventional ways ?

the table containing cities follow:

create table cities ( id int primary key, article text,           - contain article if there's one, or null if not city_name text,         - city name capitalized article_upper text,     - article in upper case city_name_upper text,   - city's name in upper-cases city_slug text,         - combination of article + space + city name in caps zipcode text,           - zip-code country text            - country city located ); 

what did considering space or ',' separators , split string in several words, , try match these words different fields.

that worked kinda ok except names articles or composed of several words, since article (or other words) considered different field completely.

what solution problem, knowing forcing user input or using separate fields no go client ?

this perfect full text search... http://msdn.microsoft.com/en-us/library/ms142571.aspx

what create summary table concatenated strings columns in other table, , put full-text index on that. and, don't use text data type in main table, use regular strings - varchar(8000) or whatever...

select id, article + ' ' + city_name  + ' ' + zipcode  + ' ' + country summary cities 

(you not need include upper case versions, 1 version of each word.)