CODEWITHSUNDEEP
sqloracle-databasewildcard
Kiranshell
Kiranshell

Reputation: 267

Search (in Oracle), using wild card without worrying about the case or order in which the words appear

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

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.

Upvotes: 2

Views: 2108

Answers (2)

Anjan Biswas
Anjan Biswas

Reputation: 7912

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.

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

Upvotes: 0

podiluska
podiluska

Reputation: 51494

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

Or you can investigate Oracle Text

Upvotes: 3

Related Questions