The LIKE Command in SQL

This guide covers the like command in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

• Percent (%)
• 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:

STTStatement 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