Jan Richter Jan Richter - 4 years ago 71
SQL Question

SQL - LIKE search in address fields

I have SQL query, which searchs in table with address fields. My problem is in City field.
I assumed search column streets with house number and without him but I forgot the division of neighborhoods like: Kolin IV, KolĂ­n II, etc. My question is, can somehow from the column using the SQL function to compare only the name of the city without any indication to which the district belongs?

$where = " WHERE (LOWER(city) LIKE LOWER('%$fullAddress%')
OR LOWER(street) LIKE LOWER('%$fullAddress%')
OR CAST(postal_code AS TEXT) LIKE LOWER('%$fullAddress%')
OR CAST(house_number AS TEXT) LIKE LOWER('%$fullAddress%')
OR (LOWER(street || ', ' || city) LIKE LOWER('%$fullAddress%'))
OR (LOWER(street || ' ' || CAST(house_number AS TEXT)) LIKE LOWER('%$fullAddress%'))
OR (LOWER(city || ' ' || CAST(postal_code AS TEXT)) LIKE LOWER('%$fullAddress%'))
OR (LOWER(street || ', ' || city || ' ' || CAST(postal_code AS TEXT)) LIKE LOWER('%$fullAddress%'))
OR (LOWER(street || ' ' || CAST(house_number AS TEXT) || ', ' || city) LIKE LOWER('%$fullAddress%'))
OR (LOWER(street || ' ' || CAST(house_number AS TEXT) || ', ' || city || ' ' || CAST(postal_code AS TEXT)) LIKE LOWER('%$fullAddress%'))) AND deleteby is null";
$results = $fce->_slctSQL("public.configurations_view", "", "services", $where, "", " ORDER BY city ASC, street ASC, house_number ASC, postal_code ASC", "");

Answer Source

You might find that this works:

 OR (LOWER(city || ' ' || CAST(postal_code AS TEXT)) LIKE LOWER(REPLACE('%$fullAddress%', ' ', '%'))

However, this could introduce other issues, if cities have multi-word names.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download