I've got a query that takes an input searches for the closet match in zipcode/region/city/metrocode in a location table containing a few tens of thousands of entries (should be nearly every city in the US). The query I'm using is:
select
metrocode,
region,
postalcode,
region_full,
city
from
dv_location
where
(
region ilike '%Chicago%'
or
postalcode ilike '%Chicago%'
or
city ilike '%Chicago%'
or
region_full ilike'%Chicago%'
)
and metrocode is not null
Odd thing is, the results set I'm getting back looks like this:
metrocode;region;postalcode;region_full;city
862;CA;95712;California;Chicago Park
862;CA;95712;California;Chicago Park
602;IL;60611;Illinois;Chicago
602;IL;60610;Illinois;Chicago
What am I doing wrong? My thinking is that Chicago would have greater weight than Chicago Park since Chicago is an exact match to the term (even though I'm asking for a wildcard match on the term).