Oracle:喜欢，其中一个字符串的任何部分与另一个字符串的任何部分匹配 [英] Oracle: LIKE where any part of one string matches any part of another string

问题描述

我正在使用PL/SQL v7.1

I am using PL/SQL v7.1

我正在尝试查找在地址行字段之一和国家/地区字段中输入了国家/地区名称的所有地址记录. 问题是国家/地区详细信息输入不一致，例如.

I am trying to find all address records where the country name has been entered in one of the address line fields, and also the country field. The problem is that the country details have not been entered consistently eg.

addr4       addr5            country
----------  ----------       ---------------
JERSEY                       UK(JERSEY)
IRELAND                      REPUBLIC OFIRELAND
DOUGLAS      ISLE OF MAN     UK(ISLE OF MAN)  

因此，我需要找到在addr4或addr5中也可以找到国家/地区"字段的任何部分的记录.

So, I need to find the records where ANY PART of the Country field is also found in either addr4 or addr5.

我从这里开始

SELECT * 
FROM test_addresses          
WHERE addr4||addr5 LIKE '%'||country||'%'

我知道这是行不通的，因为它将以第一条记录为例，检查是否在addr4 || addr5中找到了"UK(JERESEY)"，所以找不到匹配项.但是，如何检查是否在addr4 || addr5中找到了'JERSEY'

I know this doesn't work because it will, taking the 1st record as an example, check if 'UK(JERESEY)' is found in addr4||addr5 and ,so, no match will be found. But how do I make it check if 'JERSEY' is found in addr4||addr5

推荐答案

尝试这种方式:

SELECT * 
FROM test_addresses
WHERE (addr4 is not null and country like '%'||addr4||'%')
or (addr5 is not null  and country like '%'||addr5||'%')

Sql Fiddle演示

这篇关于Oracle:喜欢，其中一个字符串的任何部分与另一个字符串的任何部分匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！