SQL Wildcards

What is SQL Wildcard?

A wildcard character is one that can be substituted for one or more characters in a string.
The LIKE operator accepts wildcard characters. . In a WHERE clause, the LIKE operator is used to find a certain pattern in a column.

Wildcard Characters in MS Access

🚀 Symbol : *
Description : There are one or more characters in this phrase. .
Example :su* finds sub, super, and sup

🚀 Symbol : ?
Description : Identifies a single character.
Example :c?t finds cat, and cut

🚀 Symbol : []
Description : Within the brackets, any single character is represented.
Example :s[ia]t finds sit and sat, but not shut

🚀 Symbol : [!]
Description : Any character that isn't in the brackets is represented by this character.
Example :b[!u]t finds bat, but not but.

🚀 Symbol : -
Description : Any single character within the specified range is represented.
Example :p[a-b]t finds pet and put.

🚀 Symbol : #
Description : Any single numeric character is represented.
Example : 52#5 finds 5205, 5215, 5225, 5235, 5245, 5255, 5265, 5275, 5285 and 5295.

Wildcard Characters in SQL Server

🚀 Symbol : %
Description : There are one or more characters in this phrase.
Example :su% finds sub, super, and sup

🚀 Symbol : _
Description : Identifies a single character.
Example :c_t finds cat, and cut

🚀 Symbol : []
Description : Within the brackets, any single character is represented.
Example :s[ia]t finds sit and sat, but not shut

🚀 Symbol : ^
Description : Any character that isn't in the brackets is represented by this character.
Example :b[^u]t finds bat, but not but.

🚀 Symbol : -
Description : Any single character within the specified range is represented.
Example :p[a-b]t finds pet and put.

Basic Syntax :

SELECT column1, column2...column n FROM table
WHERE column LIKE 'sbd%'

or 

SELECT column1, column2...column n FROM table
WHERE column LIKE '%sdgc%'

or

SELECT column1, column2...column n FROM table
WHERE column LIKE 'abgd_'

or

SELECT column1, column2...column n FROM table
WHERE column LIKE '_abgd'

or

SELECT column1, column2...column n FROM table
WHERE column LIKE '_abgd_'
        

Here are some instances of LIKE operators with the wildcards 'percent' and '_':

1 . WHERE Average LIKE '345%'
Returns values that start with 345.

2. WHERE Average LIKE '%230%'
Returns values that have 230 in any position.

3. WHERE Average LIKE '_56%'
Returns values that have 56 in the second and third positions.

4. WHERE Average LIKE '6_%_%'
Returns values that start with 6 and are at least 3 characters in length.

5 WHERE Average LIKE '%7'
Returns values that end with 7.

6. WHERE Average LIKE '_6%7'
Returns values that have a 6 in the second position and end with a 7.

7. WHERE Average LIKE '6___7'
Returns values in a five-digit number that start with 6 and end with 7.

Using the SQL % and _ Wildcard

The SQL statement below selects all employees whose names begin with the letter "R":

Example :

SELECT * FROM Employee_info
WHERE Employe_name LIKE 'R%'; 
                

The SQL statement below selects all employees whose names containing the pattern "sh":

Example :

SELECT * FROM Employee_info
WHERE Employe_name LIKE '%sh%'; 
                

The SQL query below selects all employees with a city that begins with any character and ends with "ondon":

Example :

SELECT * FROM Employee_info
WHERE City LIKE '_ondon'; 
                

The following SQL statement selects all employees with a City that begins with "L," then any character, then "n," then any character, then "on":

Example :

SELECT * FROM Employee_info
WHERE Employe_name LIKE 'L_n_on'; 
                

Using the [charlist] and [!charlist] Wildcard

The SQL statement below selects all employees whose names begin with "m," "s," or "t":

Example :

SELECT * FROM Employee_info
WHERE Employe_name LIKE '[mst]%'; 
                

The SQL statement below selects all employees whose names begin with "l," "m,", "n" ot "o":

Example :

SELECT * FROM Employee_info
WHERE Employe_name LIKE '[l-o]%';
                

The SQL statement below selects all employees whose names, not begin with "m," "s," or "t":

Example :

SELECT * FROM Employee_info
WHERE Employe_name LIKE '[!mst]%'; 
                

The SQL statement below selects all employees whose names not begin with "l," "m,", "n" ot "o":

Example :

SELECT * FROM Employee_info
WHERE Employe_name LIKE '[!l-o]%';
                

With this article at Logicmojo, you must have the complete idea of SQL Wildcards.