SQL - 在地址字段中进行 LIKE 搜索

Question

我有 SQL 查询，它在带有地址字段的表中进行搜索。我的问题在城市领域。我假设搜索列街道有门牌号但没有他但我忘记了街区的划分，如:Kolin IV、Kolín II 等。我的问题是，可以从列中使用 SQL 函数以某种方式只比较没有城市的名称任何指示该地区属于哪个地区？

$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 1

您可能会发现这行得通:

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

但是，如果城市有多个单词名称，这可能会引入其他问题。