In This Article, You Will Know About SQL Wildcard.
SQL Functions – Before moving ahead, let’s know a bit about SQL LIKE Operator.
Table of Contents
SQL Wildcard Characters
In SQL, wildcard characters are the alternative one or more characters to find or match the string.
Wildcard characters are used with the LIKE operator. And LIKE operator is used with the WHERE clause to match the specified character in the column.
Wildcard Characters in SQL Server
LIKE Operator | Description |
% | Represents zero or more characters |
_ | Represents a single character |
[] | Represents any single character within the brackets |
^ | Represents any character, not in the brackets |
– | Represents any single character within the specified range |
Example of wildcards with the LIKE operator.
LIKE Operator | Description |
WHERE CustomerName LIKE ‘A%’ | Finds any values that start with “A” |
WHERE CustomerName LIKE ‘%x’ | Finds any values that end with “x” |
WHERE CustomerName LIKE ‘%e%’ | Finds any values that have “e” in any position |
WHERE CustomerName LIKE ‘_l% | Finds any values that have “_l” in the second position |
WHERE CustomerName LIKE ‘R____%’ | Finds any values that start with “R” and are at least 5 characters in length |
WHERE ContactName LIKE ‘A%x’ | Finds any values that start with “A” and end with “x” |
Example Database
Below is the example of a database table named “details.”
Id | Name | Class | section_id |
1 | Alex | 5 | 1 |
2 | Rolex | 6 | 2 |
3 | Salex | 7 | 2 |
4 | Lelex | 8 | 3 |
5 | Kelex | 9 | 5 |
Wildcard %
Example: Use wildcard % to match the specified character.
SELECT * FROM details
WHERE Name LIKE 'A%';
As a result, it returns all names starting with “A” from the whole table.
Example: Use wildcard % to match the specified character.
SELECT Name
FROM details
WHERE Name LIKE 'A%';
As a result, it returns all names starting with “A” from the “Name” column.
Example: Use wildcard % to match the specified character.
SELECT Name
FROM details
WHERE Name LIKE '%e%';
As a result, it returns all names that contain “e” at any position from the “Name” column.
Wildcard _ (Underscore)
Example: Use wildcard _ (underscore) to represent the single character.
SELECT * FROM details
WHERE Name LIKE '_lex%';
As a result, it returns all names that contain the first character “A” from the whole table.
Example: Use wildcard _ (underscore) to represent the single character.
SELECT * FROM details
WHERE Name LIKE 'A_ex%';
As a result, it returns the “Alex” name that contains the second character “l” from the whole table.
The following SQL wildcard selects the name “Rolex” with starting “R”, followed by any character, followed by “l”, followed by any character, followed by “x”.
Example: Use wildcard _ (underscore) to represent the single character.
SELECT * FROM details
WHERE Name LIKE 'R_l_x%';
As a result, it returns the “Rolex” name that contains the second character “l” from the whole table.
Example: Use wildcard [charlist] to represent any single character from the bracket.
SELECT * FROM details
WHERE Name LIKE '[ARS]%';
As a result, it returns all names from starting characters “A”, “R”, and “S”.
Example: Use wildcard [charlist] to represent any single character within the range of the bracket.
SELECT * FROM details
WHERE Name LIKE '[A-L]%';
As a result, it returns all names from starting characters within the range.
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
Connect on:
