SQL

SQL Wildcards

🌟 SQL Wildcards are special characters used with the LIKE operator to search for data that matches a specific pattern. Wildcards make SQL queries more flexible by allowing you to search for partial text, unknown characters, prefixes, suffixes, and other text patterns instead of requiring exact matches.

📖 What are SQL Wildcards?

Wildcards represent one or more unknown characters in a search pattern. They are commonly used in SELECT statements with the WHERE clause and the LIKE operator to perform pattern matching on string data.

Information

SQL wildcards are primarily used with character data types such as CHAR, VARCHAR, and TEXT.

📝 Basic Syntax

General Syntax

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

🌟 Common SQL Wildcards

The SQL standard defines two primary wildcard characters used with the LIKE operator.

Wildcard	Description	Example Pattern
%	Matches zero or more characters.	'A%'
_	Matches exactly one character.	'J_n'

📊 Sample Table

Consider the following Students table:

StudentID	Name	Department	City
101	Alice	Computer Science	Chennai
102	Bob	Mathematics	Coimbatore
103	Charlie	Physics	Madurai
104	David	Computer Science	Chennai
105	Eva	Mathematics	Salem

🔤 The % Wildcard

The % wildcard represents zero or more characters. It is the most commonly used wildcard in SQL.

1️⃣ Starts With

Names Starting with A

SELECT *
FROM Students
WHERE Name LIKE 'A%';

This query returns names such as Alice.

2️⃣ Ends With

Names Ending with e

SELECT *
FROM Students
WHERE Name LIKE '%e';

3️⃣ Contains Text

Names Containing ar

SELECT *
FROM Students
WHERE Name LIKE '%ar%';

4️⃣ Multiple Words

Department Contains Science

SELECT *
FROM Students
WHERE Department LIKE '%Science%';

🔠 The _ Wildcard

The _ wildcard represents exactly one character.

Example 1

Single Character Match

SELECT *
FROM Students
WHERE Name LIKE '_ob';

This matches names such as Bob.

Example 2

Exactly Five Characters

SELECT *
FROM Students
WHERE Name LIKE '_____';

This query returns names that contain exactly five characters.

Example 3

Pattern with Multiple Underscores

SELECT *
FROM Students
WHERE Name LIKE '__a__';

This matches five-character names where the third character is a.

🔀 Combining Wildcards

Wildcards can be combined to create more advanced search patterns.

Combined Wildcards

SELECT *
FROM Students
WHERE Name LIKE 'A_%';

This query returns names that begin with A and contain at least one additional character.

🚫 Using NOT LIKE with Wildcards

Use NOT LIKE to exclude rows that match a specific pattern.

Exclude Names Starting with A

SELECT *
FROM Students
WHERE Name NOT LIKE 'A%';

🔗 Wildcards with AND

Wildcard with AND

SELECT *
FROM Students
WHERE Name LIKE 'A%'
AND City = 'Chennai';

🔗 Wildcards with OR

Wildcard with OR

SELECT *
FROM Students
WHERE Name LIKE 'A%'
OR Name LIKE 'D%';

📊 Common Wildcard Patterns

Pattern	Meaning
'A%'	Starts with A.
'%e'	Ends with e.
'%ar%'	Contains "ar".
'J_n'	Three-letter word beginning with J and ending with n.
'_____'	Exactly five characters.
'A_%'	Starts with A and has at least one more character.

⚠️ Database-Specific Wildcards

While % and _ are defined by the SQL standard and are widely supported, some database systems provide additional pattern-matching features outside the standard.

Database	Standard Wildcards	Additional Features
MySQL	%, _	Supports regular expressions with separate operators/functions.
PostgreSQL	%, _	Supports ILIKE for case-insensitive matching and regular expressions.
SQL Server	%, _	Also supports bracket expressions such as [A-C] in LIKE patterns.
Oracle	%, _	Supports regular expression functions in addition to LIKE.

Important

Features such as bracket expressions, ILIKE, and regular expression support are database-specific and are not part of the SQL standard.

💼 Real-World Example

A university wants to find all students whose names begin with D or whose department contains the word Science.

Real-World Query

SELECT StudentID,
       Name,
       Department
FROM Students
WHERE Name LIKE 'D%'
OR Department LIKE '%Science%'
ORDER BY Name;

This query performs flexible searches without requiring exact text matches.

⚠️ Best Practices

Best Practice

Use % for partial text searches and _ when exactly one unknown character is expected. Whenever possible, avoid patterns that begin with % because they may reduce index usage in many database systems. Use database-specific wildcard features only when portability is not a concern.

🚀 Key Points to Remember

📌 Wildcards are used with the LIKE operator.
📌 % matches zero or more characters.
📌 _ matches exactly one character.
📌 Wildcards can be combined to create advanced search patterns.
📌 NOT LIKE excludes matching patterns.
📌 Some wildcard features are specific to certain database systems.

>>"SQL wildcards let you search for patterns instead of exact values, making text searches far more flexible and powerful."

Summary

✅ SQL wildcards are essential for pattern matching in text data. By mastering the standard wildcards % and _, and understanding database-specific extensions, you can build flexible, efficient, and user-friendly search queries for a wide range of applications.