The PostgreSQL LIKE administrator is utilized to coordinate content qualities against an example utilizing special cases. In the event that the inquiry articulation can be coordinated to the example articulation, the LIKE administrator will restore valid, which is 1.
There are two special cases utilized related to the LIKE administrator −
- The percent sign (%)
- The underscore (_)
The percent sign addresses zero, one, or different numbers or characters. The underscore addresses a solitary number or character. These images can be utilized in blends.
In the event that both of these two signs isn't utilized related to the LIKE condition, at that point the LIKE demonstrations like the equivalents administrator.
Syntax
The essential language structure of % and _ is as per the following −
SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'
You can consolidate N number of conditions utilizing AND OR administrators. Here XXXX could be any numeric or string esteem.
Example
Here are number of models indicating WHERE part having distinctive LIKE provision with '%' and '_' administrators −
S. No. | Statement & Description |
---|---|
1 |
WHERE SALARY::text LIKE '200%' Finds any values that start with 200 |
2 |
WHERE SALARY::text LIKE '%200%' Finds any values that have 200 in any position |
3 |
WHERE SALARY::text LIKE '_00%' Finds any values that have 00 in the second and third positions |
4 |
WHERE SALARY::text LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length |
5 |
WHERE SALARY::text LIKE '%2' Finds any values that end with 2 |
6 |
WHERE SALARY::text LIKE '_2%3' Finds any values that have 2 in the second position and end with a 3 |
7 |
WHERE SALARY::text LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3 |
Postgres LIKE is String think about as it were. Consequently, we need to expressly project the whole number segment to string as in the models above.
Allow us to take a genuine model, think about the table COMPANY, having records as follows −
# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
Coming up next is a model, which would show all the records from COMPANY table where AGE begins with 2 −
testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';
This would deliver the accompanying outcome −
id | name | age | address | salary
----+-------+-----+-------------+--------
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
8 | Paul | 24 | Houston | 20000
(7 rows)
Coming up next is a model, which would show all the records from COMPANY table where ADDRESS will have a hyphen (- ) inside the content −
testdb=# SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';
This would deliver the accompanying outcome −
id | name | age | address | salary
----+------+-----+-------------------------------------------+--------
4 | Mark | 25 | Rich-Mond | 65000
6 | Kim | 22 | South-Hall | 45000
(2 rows)