In Testing, proficient use of SQL queries will help you speed up testing in some functions to query and search data. The wildcard characters that aid in finding commonly used data are:
%: The percent sign indicates zero, one or more characters.
_: The underscore represents a single character.
[charlist]: Determine the set and character range to match.
[! charlist]: Specifies a set and range of characters that do NOT match.
- These wildcards are used in conjunction with the LIKE operator.
- The characters “%” and “_” are used in both MySQL and SQL Server.
- The characters “[charlist]” or “[! Charlist]” are only used in SQL Server.
I. The character “%”
1. Syntax and Description
Syntax | The LIKE operator | Description |
---|---|---|
first | WHERE column_name LIKE ‘a%’ | Find the value starting with “a” |
2 | WHERE column_name LIKE ‘% a’ | Find the value ending with “a” |
3 | WHERE column_name LIKE ‘% a%’ | Find the value with “a” in any position |
4 | WHERE column_name LIKE ‘a% o’ | Find the value starting with “a” and ending with “o” |
2. Application examples
Database Table
Syntax 1: Find all QA with qa_full_name starting with “T”:
1 2 | SELECT * FROM thuong.qa_member where qa_full_name LIKE 'T%'; |
Syntax 2: Find all QAs with qa_full_name ending in “Trinh”:
1 2 | SELECT * FROM thuong.qa_member where qa_full_name LIKE '%Trinh'; |
Syntax 3: Find all QAs with qa_full_name containing “Thi” in any position:
1 2 | SELECT * FROM thuong.qa_member WHERE qa_full_name LIKE '%Thị%'; |
Syntax 4: Find all QAs that have qa_full_name starting with “Nguyen” and ending with “h”:
1 2 | SELECT * FROM thuong.qa_member WHERE qa_full_name LIKE 'Nguyễn%h'; |
II. Characters “_”
1. Syntax and Description
Syntax | The LIKE operator | Description |
---|---|---|
first | WHERE column_name LIKE ‘_a%’ | Find any value with “a” in position 2 |
2 | WHERE column_name LIKE ‘a% _%’ | Look for values that start with “a” and have at least 3 characters |
3 | WHERE column_name LIKE ‘_a% o’ | Find the value “a” in the 2nd position and end with “o” |
2. Application examples
Syntax 1: Find all QA with qa_short_name containing “u” in the 2nd position:
1 2 | SELECT * FROM thuong.qa_member WHERE qa_short_name LIKE '_u%'; |
Syntax 2: Find all QAs that have qa_short_name starting with “T” and have at least 3 characters:
1 2 | SELECT * FROM thuong.qa_member WHERE qa_short_name LIKE 'T%_%'; |
Syntax 3: Find all QAs that have qa_short_name with “u” in the 2nd position and end with “g”:
1 2 | SELECT * FROM thuong.qa_member WHERE qa_short_name LIKE '_u%g'; |
II. The character “[charlist]”
Syntax: Find all QAs with qa_short_name starting with “T”, “L”
SELECT * FROM thuong.qa_member WHERE qa_short_name LIKE '[TL]%';
The result will be:
II. “[! Charlist]”
Syntax: Find all QAs that have qa_short_name DO NOT begin with “T”, “L”
SELECT * FROM thuong.qa_member WHERE qa_short_name LIKE '[!TL]%';
or
SELECT * FROM thuong.qa_member WHERE qa_short_name NOT LIKE '[TL]%';
The result will be:
Refer: https://o7planning.org/en/10239/huong-dan-hoc-sql-cho-nguoi-moi-bat-dau-voi-sql-server https://www.codehub.vn/Hoc-SQL / Ky-Tu-Dai-Dien-Wildcard-in-SQL https://www.w3schools.com/sql/sql_wildcards.asp