搜索（在Oracle中），使用外卡而不用担心单词出现的情况或顺序

How to search using wild card (in Oracle), without worrying about case or order in which the words appear.

如何使用通配符(在Oracle中)进行搜索,而不必担心单词出现的大小写或顺序。

e.g. if I search for like '%a%b%', it should return values containing *a*b*, *A*B* ,*b*a* and *B*A* This is just a sample, the search may have 5 or more words, is it possible get the result in just one expression rather than using AND.

例如如果我搜索'%a%b%',它应该返回包含* a * b *,* A * B *,* b * a *和* B * A *的值这只是一个示例,搜索可能有5个或更多的单词,是否有可能只在一个表达式中得到结果而不是使用AND。

2 个解决方案

#1

select * 
from yourtable
where yourfield like '%a%'
and yourfield like '%b%'

Or you can investigate Oracle Text

或者您可以调查Oracle Text

#2

select * from table
 where upper(column) like '%A%B%';

or

select * from table
 where lower(column) like '%a%b%';

or

select * from table
 where upper(column) like '%A%'
   and upper(column) like '%B%';

or

select * from table
 where lower(column) like '%a%'
   and lower(column) like '%b%';

However, if you would like to find out *a*b* or *b*a* and so on. The other solution would be to sort the CHARS in the VARCHAR first i.e. if you have a string like aebcd then sort it to abcde and then do pattern match using like. As per this you can use below query to sort the chars in a varchar and then do the pattern match on it.

但是,如果您想找出* a * b *或* b * a *等等。另一个解决方案是首先在VARCHAR中对CHARS进行排序,即如果你有一个像aebcd这样的字符串,那么将它排序为abcde,然后使用like进行模式匹配。按此,您可以使用以下查询对varchar中的字符进行排序,然后对其进行模式匹配。

SELECT 1 val
FROM (SELECT MIN(permutations) col
        FROM (SELECT REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
                FROM (SELECT LEVEL l, SUBSTR ('cba', LEVEL, 1) n
                        FROM DUAL   --replace dual by your table
             CONNECT BY LEVEL <= LENGTH ('cba')) yourtable
      CONNECT BY NOCYCLE l != PRIOR l)
        WHERE LENGTH (permutations) = LENGTH ('cba')) temp_tab
  WHERE upper(col) like '%A%B%';

Returns

val 
------------------
1