The WILDCARD operator is in SQL

WILDCARD , also known as a wildcard, is used in conjunction with the LIKE statement in SQL to compare a value with similar values.

SQL supports two representation operators associated with the LIKE operator as percent sign (%) and underscore (_).

Wildcard Description Percentage symbol (%) Represents one or more characters.
Note : MS Access uses an asterisk character (*) to replace the percentage character (%) Underscore (_) Represents a single character.
Note : MS Access uses a question mark (?) Instead of underscores (_)

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.

The syntax for using WILDCARD in SQL

The basic syntax of '%' 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.

Example of WILDCARD operator

The following table has a few examples showing the WHERE clause has a LIKE clause with different WILDCARD '%' 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 us take a practical example, considering the table NHANVIEN has the following records:

 +----+----------+-----+-----------+----------+ | 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 functions that handle Date in SQL , so please keep track.

Last post: TRANSACTION in SQL

Next article: Function handling DATE / TIME in SQL - Part 1