How to bring weight to full matches over partial matches (PostgreSQL)

Question

I have a query that executes input queries to match a cabinet in zipcode / region / city / metrocode in a location table containing several tens of thousands of records (there should be almost every city in the USA). I am using the following query:

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

The odd thing, the results that I return are as follows:

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? I think that Chicago will have more weight than Chicago Park, since Chicago is an exact match for this term (although I ask for a wildcard for this term).

Answer 1

Try the following:

select
  metrocode,
  region,
  postalcode,
  region_full,
  city,
  (region = 'Chicago'
   OR postalcode = 'Chicago'
   OR city = 'Chicago'
   OR region_full = 'Chicago') AS full_match
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
 order by full_match desc;

Answer 2

order by.

...
order by
case
  when city ilike 'Chicago' then 1
  when city ilike 'Chicago%' then 2
  when city ilike '%Chicago%' then 3
  else 4
end

, ...