The WILDCARD Operator Is in SQL

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

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 (_).

WildcardDescription 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%'

SELECT FROM ten_bang
WHERE cot LIKE 'XXXX_'

SELECT FROM ten_bang
WHERE cot LIKE '_XXXX'

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:

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 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