Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

search criteria difference between Like vs Contains() in oracle

Tags:

sql

oracle

I created a table with two columns.I inserted two rows.

id     name
1      narsi reddy
2      narei sia

one is simply number type and another one is CLOB type.So i decided to use indexing on that. I queried on that by using contains. query:

select * from emp where contains(name,'%a%e%')>0

2      narei sia

I expected 2 would come,but not. But if i give same with like it's given what i wanted. query:

select * from emp where name like '%a%e%'

ID                     NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
1                      (CLOB) narsi reddy                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
2                      (CLOB) narei sia                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

2 rows selected

finally i understood that like is searching whole document or paragraph but contains is looking in words.

so how can i get required output?

like image 504
narsireddy Avatar asked Apr 18 '11 09:04

narsireddy


People also ask

What is the difference between like and contains?

The 'Contains' operator adds a wild character automatically to the search string, while the 'Like' operator matches for the exact search string.

What is the difference between like and contains in SQL?

LIKE is an operator which is used to find whether a character string matches a specified pattern. CONTAINS is a predicate which can be used to search for a word, prefix of a word, a word near another word, synonym of a word, etc..

How can I find the difference between two values in Oracle?

To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .

What can I use instead of like operator Oracle?

INSTR is another option but you cannot have an index which suits this comparison. Save this answer.


2 Answers

LIKE and CONTAINS are fundamentally different methods for searching.

LIKE is a very simple string pattern matcher - it recognises two wildcards (%) and (_) which match zero-or-more, or exactly-one, character respectively. In your case, %a%e% matches two records in your table - it looks for zero or more characters followed by a, followed by zero or more characters followed by e, followed by zero or more characters. It is also very simplistic in its return value: it either returns "matched" or "not matched" - no shades of grey.

CONTAINS is a powerful search tool that uses a context index, which builds a kind of word tree which can be searched using the CONTAINS search syntax. It can be used to search for a single word, a combination of words, and has a rich syntax of its own, such as boolean operators (AND, NEAR, ACCUM). It is also more powerful in that instead of returning a simple "matched" or "not matched", it returns a "score", which can be used to rank results in order of relevance; e.g. CONTAINS(col, 'dog NEAR cat') will return a higher score for a document where those two words are both found close together.

like image 156
Jeffrey Kemp Avatar answered Sep 20 '22 16:09

Jeffrey Kemp


I believe that your CONTAINS query is matching 'narei sia' because the pattern '%a%e%' matches the word 'narei'. It does not match against 'narsi reddy' because neither word, taken individually, matches the pattern.

I assume you want to use CONTAINS instead of LIKE for performance reasons. I am not by any means an expert on CONTAINS query expressions, but I don't see a simple way to do the exact search you want, since you are looking for letters that can be in the same word or different words, but must occur in a given order. I think it may be best to do a combination of the two techniques:

WHERE CONTAINS(name,'%a% AND %e%') > 0
  AND name LIKE '%a%e%'

I think this would allow the text index to be used to find candidate matches (anything which has at least one word containing 'a' and at least one word containing 'e'). These would would then be filtered by the LIKE condition, enforcing the requirement that 'a' precede 'e' in the string.

like image 44
Dave Costa Avatar answered Sep 21 '22 16:09

Dave Costa