Pattern search (like - not like)

There is a relational operator that is used to perform string-only comparisons, "like" and "not like".

We have made queries using relational operators to compare strings. 

Examples:

we know how to retrieve books whose author is equal to the string "Borges":

 select * from books

  where author = 'Borges';

The equal operator ("=") allows us to compare strings of characters, but when performing the comparison, it looks for matches of complete strings, it performs an exact search.

Let's imagine that we have these 2 books registered:

 "El Aleph", "Borges";

 "Poetic Anthology", "JL Borges";

If we want to recover all the books of "Borges" and we specify the following condition:

 select * from books

 where author = 'Borges';

Only the first record will appear, since the string "Borges" is not the same as the string "JL Borges".

This happens because the operator "=" (equal), also the operator "<>" (different) compare complete character strings. To compare portions of strings we use the "like" and "not like" operators.

So we can compare chunks of character strings to make queries. To retrieve all the records whose author contains the string "Borges" we must type:

 select * from books

  where author like "% Borges%";

The "%" (percent) symbol replaces any number of characters (including no characters). It is a wildcard character. "like" and "not like" are comparison operators that indicate equality or difference.

To select all books that start with "M":

 select * from books

  where title like 'M%';

Note that the "%" symbol is no longer at the beginning, with this we indicate that the title must have "M" as the first letter and then any number of characters.

To select all books that DO NOT start with "M":

 select * from books

  where title not like 'M%';

Just as "%" replaces any number of characters, the underscore "_" replaces one character, it is another wildcard character. For example, we want to see the "Lewis Carroll" books but we don't remember whether to write "Carroll" or "Carrolt", so we type this condition:

 select * from books

  where author like "% Carrol_";

"like" is used with character and date data types. If we use "like" with non-character data types, Oracle converts (if possible) the data type to character. For example, we want to find all books whose price is between 10.00 and 19.99:

 select title, price from books

  where price like '1 _,%';

We want books that do NOT include pennies in their prices:

 select title, price from books

  where price not like '%,%';

Null values ​​are not included in searches with "like" and "not like".