PL-SQL
Oracle PL/SQL Collections: Varrays, Nested & Index by Tables
What is Collection? A Collection is an ordered group of elements of particular data types. It can...
The PostgreSQL LIKE operator helps us to match text values against patterns using wildcards. It is possible to match the search expression to the pattern expression.
If a match occurs, the LIKE operator returns true. With the help of LIKE operator, it is possible to use wildcards in the WHERE clause of SELECT, UPDATE, INSERT or DELETE statements.
In this PostgreSQL Tutorial, you will learn the following:
There are only two wildcards that can be used together with
The percent sign (%) is used to represent zero, one, or many characters or numbers.
The underscore wildcard (_) is used to represent one character or number. These symbols can also be combined. If the LIKE operator is not used together with these two signs, it will act like the equals operator.
Here is the syntax for the LIKE operator:
expression LIKE pattern [ ESCAPE 'escape-character' ]
The expression is a character expression like a column or field.
The pattern is a character expression with pattern matching.
The escape-character is an optional parameter. It allows for testing of literal instances of wildcard characters such as % and _. If it is not provided, the \ will be used as the escape character.
As we stated earlier, the % sign matches zero, one or more characters or numbers. Consider the following table:
Book:
We want the book whose name is like "Lear…" to get that result, we can run the following command:
SELECT * FROM Book WHERE name LIKE 'Lear%';
This will return the following:
The book was found.
Let us search for a book "by" in its name:
SELECT * FROM Book WHERE name LIKE '%by%';
This will return the following:
As we stated earlier, the _ sign represents one character or number. It can be used as shown below:
SELECT * FROM Book WHERE name LIKE '_earn%';
This returns the following:
Here is another example:
SELECT * FROM Book WHERE name LIKE '%Beginner_';
This returns the following:
When the LIKE operator is combined with the NOT operator, any row that does not match the search pattern is returned. For example, to see a book whose name does not begin with "post", we can run the following command:
SELECT * FROM Book WHERE name NOT LIKE 'Post%';
This returns the following:
Only one book met the search condition. Let us see the list of books name who don't have a word "Made":
SELECT * FROM Book WHERE name NOT LIKE '%Made%';
This returns the following:
3 rows met the search condition.
Now let's see how the actions can be performed using pgAdmin.
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
SELECT * FROM Book WHERE name LIKE 'Lear%';
Step 4) Click the Execute button.
It should return the following:
To search for a book "by" in its name:
Step 1) Type the following command in the query editor:
SELECT * FROM Book WHERE name LIKE '%by%';
Step 2) Click the Execute button.
It should return the following:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
SELECT * FROM Book WHERE name LIKE '_earn%';
Step 4) Click the Execute button.
It should return the following:
Step 5) To run the second example:
SELECT * FROM Book WHERE name LIKE '%Beginner_';
It should return the following:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) To see all books whose names don't start with "Post", type the query in the query editor:
SELECT * FROM Book WHERE name NOT LIKE 'Post%';
Step 4) Click the Execute button.
It should return the following:
To see the list of books whose names don't have the word "Made":
Step 1) Type the following query in the query editor:
SELECT * FROM Book WHERE name NOT LIKE '%Made%';
Step 2) Click the Execute button.
It should return the following:
Download the Database used in this Tutorial
What is Collection? A Collection is an ordered group of elements of particular data types. It can...
What is Database Design? Database Design is a collection of processes that facilitate the...
$20.20 $9.99 for today 4.5 (108 ratings) Key Highlights of PL/SQL Tutorial PDF 188+ pages eBook...
SQL stands for Structured Query Language is a domain specific programming language for managing...
What is Nested Blocks Oracle? In PL/SQL, each block can be nested into another block. They are...
What is WHERE Clause in MySQL? WHERE Clause in MySQL is a keyword used to specify the exact...