The LIKE command in SQL

In SQL, the LIKE command is used to compare a value with similar values ​​using wildcards. There are two wildcards used in conjunction with LIKE:

1. Percent (%)
2. Underscore (_)

A percent sign can represent a string of characters of any length (including length 0). An underscore represents a number or a character. These symbols can be used in combination.

LIKE command syntax in SQL

The basic syntax of the LIKE command with '%' and '_' is as follows:

 SELECT FROM ten_bang 
 WHERE cot LIKE 'XXXX%' 
 
 or 
 
 SELECT FROM ten_bang 
 WHERE cot LIKE '%XXXX%' 
 
 or 
 
 SELECT FROM ten_bang 
 WHERE cot LIKE 'XXXX_' 
 
 or 
 SELECT FROM ten_bang 
 WHERE cot LIKE '_XXXX' 
 
 or 
 
 SELECT FROM ten_bang 
 WHERE cot LIKE '_XXXX_' 

You can combine N conditions using AND or OR operators. Here, XXXX can be any numeric or string value.

Examples of LIKE in SQL

The following table has a few examples that show the WHERE clause has a LIKE clause with different '%' and '_' operators:

STT Statement and description 1 WHERE SALARY LIKE '200%'
Find all values ​​starting with 200. 2 WHERE SALARY LIKE '% 200%'
Find all values ​​that have 200 any position. 3 WHERE SALARY LIKE '_00%'
Find all values ​​that have 00 in the second and third positions. 4 WHERE SALARY LIKE '2 _% _%'
Find all values ​​that begin with 2 and have at least 3 characters. 5 WHERE SALARY LIKE '% 2'
Find all values ​​ending in 2. 6 WHERE SALARY LIKE '_2% 3'
Find all values ​​with position 2 in the second position and end with 3. 7 WHERE SALARY LIKE '2___3'
Find all values ​​in the five-digit number beginning with 2 and ending with 3.

Let's take a practical example, consider the NHANVIEN table with the records as below.

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

The example shows all the records from the NHANVIEN table that LUONG starts with 200, we do the following:

 SQL> SELECT * FROM NHANVIEN 
 WHERE LUONG LIKE '200%'; 

The returned result is:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 3 | Nga | 23 | Hanam | 2000.00 | +----+----------+-----+-----------+----------+

In the next section, we will learn about the TOP command, remember to follow it.

Previous article: DELETE command in SQL

Next lesson: TOP command in SQL