PostgreSQL Basic Tutorial

PostgreSQL Advanced Tutorial

PostgreSQL Interface

PostgreSQL LIMIT Clause PostgreSQL DELETE Statement

PostgreSQL LIKE Clause

In PostgreSQL database, if we want to retrieve data containing certain characters, we can use LIKE Clause.

In LIKE clause, it is usually used in combination with wildcards, which represent any character. In PostgreSQL, there are mainly the following two types of wildcards:

Percentage %
Underscore _

If neither of the above wildcard characters is used, the result of LIKE clause and the equals sign '=' is the same.

Syntax

The following is the syntax for using LIKE clause with percentage sign % and underscore _ The general syntax for retrieving data from a database:

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 specify any condition in the WHERE clause.

You can use AND or OR to specify one or more conditions.

XXXX It can be any number or character.

Online Example

The following demonstrates some differences between % and _ in LIKE statements:

Example	Description
WHERE SALARY::text LIKE '200%}	Find the SALARY field with 2data starting with '00'.
WHERE SALARY::text LIKE '%200%}	Find the data containing 2data with '00' characters.
WHERE SALARY::text LIKE '_00%}	Find the data in the SALARY field that has '00' at the second and third positions.
WHERE SALARY::text LIKE '2 　%%'	Find the SALARY field with 2 The length of the leading character is greater than 3 of the data.
WHERE SALARY::text LIKE '%2'	Find the SALARY field with 2 ending data
WHERE SALARY::text LIKE '_2%3'	Find the SALARY field containing 2 at the second position and starting with 3 ending data
WHERE SALARY::text LIKE '2___3'	Find the SALARY field with 2 starting with3 ending and being 5 Digit data

In PostgreSQL, the LIKE clause can only be used for character comparison, so in the above example, we need to convert the integer data type to the string data type.

Create COMPANY table（Download COMPANY SQL file ），Data content as follows：

w3codeboxdb#　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)

The following example will find AGE starting with 2 Starting data:

w3codeboxdb=#　SELECT　*　FROM　COMPANY　WHERE　AGE::text　LIKE　'2%';

The following results are obtained:

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)

The following example will find the address field containing - Character data:

w3codeboxdb=#　SELECT　*　FROM　COMPANY　WHERE　ADDRESS　　LIKE　'%-%';

The result is as follows:

id　|　name　|　age　|　　|　　address　　|　|　salary
----+------+-----+-------------------------------------------+--------
　　4　|　Mark　　|　　25　|　Rich-Mond　　|　　65000
　　6　|　Kim　　|　　22　|　South-Hall　　|　　45000
(2　rows)

PostgreSQL LIMIT Clause PostgreSQL DELETE Statement