Definition of PostgreSQL String Contains
PostgreSQL provides a pattern matching function to the user which we also call as String Contains (LIKE operator). The pattern matching means we can retrieve specific data or records from the database table using different methods. String Contains play a very important role in database management systems because we store huge amounts of data and we need to retrieve specific data in minimum time, so at that time we cannot read all data line by line because it is a time-consuming process. So the best solution for this problem is String Contains means pattern-matching functions such as like operator.
Syntax:
select column name1 column name2
from specified table name where
specified column name like ‘matching pattern’;
Explanation: In the above syntax we implement a pattern like matching function by using select and where clause where column name is specified column name in the table, table name means specified table name.
PostgreSQL String Contains Function Works with Examples
- We must install PostgreSql in your system.
- We required basic knowledge about PostgreSql.
- We must require tables to perform String Contain operation.
- Like operator is a logical operator and it is used to match specified matching patterns.
Pattern matching we can implement by using different methods as follows. Basically in a matching pattern function, we use the like operator to find out the desired matching pattern from the database table.
So first we create a table by using the following statement.
CREATE TABLE emp_info (
emp_id INT PRIMARY KEY NOT NULL,
emp_name TEXT NOT NULL,
emp_age INT NOT NULL,
address CHAR(50),
salary REAL);
Now we will be inserting values into the table.
insert into emp_info (emp_id, emp_name, emp_age, address, salary) values
(1,'Poll',28,'london',30000),
(2,'Alex',30,'Hongkong',40000),
(3,'John',35,'Newyork',50000),
(4,'Bob',32,'Sydney',20000);
For selecting the table emp_info we will use the following:
select * from emp_info;
Illustrate the end result of the above declaration by using the use of the following snapshot.
Now see how we can use like operator to find out the matching pattern using the below methods.
1. LIKE
Suppose we need to find out the employee’s name and you do not remember her exact name. But you remember the first two characters like bo. So how can we find the exact name of an employee? You may find names in emp_info tables but it is a time-consuming process. So you can use the like an operator that is provided by PostgreSQL.
Like operator uses two signs as below
- %(Percentage sign): This sign is used to match any sequence of character, the character size may be zero or more.
- _(Underscore sign): This sign is used to match any single character.
Example
Consider the above example we need to find out the employee’s name and you do not remember the exact name of the employee, but you remember the first two characters for example bo. Let’s see how it works. We use the following statement to find out employee names like bo.
select emp_id, emp_name from
emp_info where emp_name like 'bo%';
Explanation: With the help of the above statement, we find those names start with ‘bo’ characters where we use select and where clause to implement the above statement and we return two-column emp_id and emp_name. Illustrate the end result of the above declaration by using the use of the following snapshot.
Consider another example, suppose we need to find those employee names that end with an ex. By using the following syntax we return ex character.
Syntax:
Like ‘%ex’
Let’s see the example, consider the following statement.
Select emp_id, emp_name from
emp_info where emp_name like '%ex';
Illustrate the end result of the above declaration by using the use of the following snapshot.
We can combine percentage and underscore signs to get the desired pattern
Considering the above example we need to find Alex’s employee name and we know the last three characters like lex. So you can use % sign to find out Alex employee but you can also use % and _ sign to find out ales user.
Select emp_id, emp_name from
emp_info where emp_name like '_oll%';
Illustrate the end result of the above declaration by using the use of the following snapshot.
2. NOT LIKE
This operator is totally opposite from like. Let’s see how it works.
Example
Suppose we need to find out all employee names whose names do not start with po. Use the following statement to implement not like operator.
Select emp_id, emp_name from
emp_info where emp_name not like 'po%' ;
Illustrate the end result of the above declaration by using the following snapshot.
3. ILIKE
This ilike operator is used in PostgreSQL in case-insensitively conditions.
Example
Suppose we need to find out bob employee name and we do not know bob character is a capital letter or a small letter. That time we used the ILIKE operator. See the following statement to implement the ILIKE operator.
select emp_id, emp_name from
emp_info where emp_name ilike 'BOB%';
The BOB% pattern matches any string beginning with BOB, Bob, and bob. But if you use only like operators, it does not return any row. Illustrate the end result of the above declaration by using the use of the following snapshot.
Let’s see what happens when we use only like operator in the same statement.
select emp_id, emp_name from
emp_info where emp_name like 'BOB%';
Illustrate the end result of the above declaration by using the use of the following snapshot. In the output, it will display nothing as mentioned above.
PostgreSQL also provides some other operators that are similar with like, ilike, not like and not ilike as shown in the table below.
| Operator Name | Substitute with Description |
| ~~ | It is used instead of like |
| ~~* | It is used instead of ilike |
| !~~ | It is used instead of not like |
| !~~* | It is used instead of not ilike |
Advantages
Some of the advantages are given below:
- Like operator is used to find or retrieve data to specified matching patterns.
- % (wildcard) is used to get all characters.
- % it is used to retrieve one or more characters from the database
- _ (Underscore) is used to match any number of single characters.
- It is a time-saving process instead of searching the whole table manually.
Conclusion
We hope from this above article you have understood about the PostgreSQL String Contain statement. From the above article, we learn the basic syntax of LIKE operator statements then we also learn how we can implement LIKE operators using different methods with multiple examples. From this article, we learn how we can handle different LIKE operators.
Recommended Articles
This is a guide to PostgreSQL String Contains. Here we discuss the definition and postgresql string contains function works along with different examples and its code implementation. You may also have a look at the following articles to learn more –
