SQL - Wildcards Characters

---

---

SQL Wildcard Characters

SQL Wildcards are special characters used as substitutes for one or more characters in a string. They are mainly used with the LIKE operator to search for specific patterns in character strings.

The behavior of wildcards may vary across databases. In some databases, the LIKE operator is case-sensitive, while in others it is not.

Common SQL Wildcard Characters

Following are the most commonly used wildcard characters in SQL:

These symbols can also be used in combination to perform complex pattern searching and matching in SQL queries.

Wildcard Support Across Databases

Different SQL databases support different sets of wildcards as shown in the table below:

SQL Wildcard Syntax

Following is the basic syntax to use wildcard characters in SQL:

SELECT * FROM table_name
WHERE column_name LIKE 'wildcard_pattern';

We can combine N number of conditions using the AND or the OR operators. Here, the [wildcard_pattern] can represent any numeric or string value.

SQL Wildcard Usage

The following table demonstrates various ways of using wildcards in conjunction with the LIKE operator within a WHERE clause:

Now, let us go through practical examples of using wildcards with the LIKE operator. Each example will show the query and explain the expected result.

Using the % Wildcard

The percent sign (%) is the most commonly used wildcard in SQL. It is used inside a LIKE condition to match zero, one, or many characters in a string.

This means it can be used to search for values that start with, end with, or simply contain a certain sequence of characters, regardless of what comes before or after. For example:

• 'A%' matches any string that starts with the letter A (such as Amit, Arun, Apple).
• '%n' matches any string that ends with the letter n (such as Rajan, Rohan).
• '%sh%' matches any string that contains the letters sh anywhere (such as Ramesh, Shyam, Krishna).

Example: Starts with K

Firstly, let us create a table named CUSTOMERS using the following query:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

The following INSERT query adds records into the CUSTOMERS table:

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table will be created as:

In the following example, we are retrieving all customers whose names start with the letter K:

SELECT * FROM CUSTOMERS WHERE NAME LIKE 'K%';

The output obtained is as follows:

Example: Ends with h

In the example below, we are selecting customers whose names end with the letter h:

SELECT * FROM CUSTOMERS WHERE NAME LIKE '%h';

We get the output as shown below:

Example: Contains bad

Following is an example to search for customers who have bad anywhere in their address:

SELECT * FROM CUSTOMERS WHERE ADDRESS LIKE '%bad%';

The output obtained is as follows:

Using the _ Wildcard

The underscore (_) wildcard is used in SQL to represent exactly one character. Unlike the percent sign (%), which can represent zero or many characters, the underscore only replaces a single character in the specified position.

This makes it useful when we want to search for values with a specific length or where only one character may vary. It is often used when the exact number of characters in the string is known, but one or more positions are uncertain. For example:

• '_a' matches any two-character string that ends with a (such as Ka, Ma).
• 'A_c' matches any three-character string starting with A and ending with c (such as Acc, Abc).
• '_00%' matches any number that has 00 in the second and third positions (such as 2000, 30005).

Example: Flexible first letter

In the following example, we are finding the record for Kaushik by allowing the first character to vary, while the rest must match exactly the same specified:

SELECT * FROM CUSTOMERS WHERE NAME LIKE '_aushik';

We get the output as shown below:

Example: Salary pattern

The example below retrieves customers whose salary follows a four-digit pattern starting with 1 and ending with 0:

SELECT * FROM CUSTOMERS WHERE SALARY LIKE '1__0';

The output obtained is as follows:

Using [] Wildcards (SQL Server / MS Access)

The square brackets ([]) wildcard is used in SQL Server and MS Access to match any single character from a specified set or range of characters. It allows us to define exactly which characters are acceptable in a certain position of the string.

This makes it useful when we want to search for values that share a similar pattern but differ by only one character within a known group or range. For example:

• '[AK]%' matches any string starting with either A or K (such as Arun, Kaushik).
• '[0-9]%' matches any string starting with a digit between 0 and 9 (such as 2000, 9thStreet).
• 'M[aeiou]%' matches any string starting with M followed by a vowel (such as Manoj, Meera, Mukesh).

Example: Starts with R or K

In the example below, we are retrieving all customers whose names start with either R or K:

SELECT * FROM CUSTOMERS WHERE NAME LIKE '[RK]%';

The output obtained is as follows:

Example: Range A-M

In the following example, we are selecting names that begin with any letter from A to M:

SELECT * FROM CUSTOMERS WHERE NAME LIKE '[A-M]%';

We get the output as shown below:

Using ^ Wildcard (SQL Server)

The caret (^) wildcard is used inside square brackets in SQL Server to represent any single character that is NOT in the specified set or range. It works as the opposite of the [] wildcard.

This allows us to exclude certain characters from a match while still allowing all others. It is useful when we want to filter out unwanted starting letters or specific groups of characters. For example:

• '[^A]%' matches any string that does not start with the letter A (such as Kaushik, Ramesh, but not Arun).
• '[^0-9]%' matches any string that does not start with a digit (such as Khilan, Muffy).
• 'M[^aeiou]%' matches any string starting with M followed by a consonant (such as Manoj would not match, but Mritun would match).

Example: Excluding K

In the example below, we are retrieving all customers whose names do not start with the letter K:

SELECT * FROM CUSTOMERS WHERE NAME LIKE '[^K]%';

The output obtained is as follows:

Combining Wildcards

You can use more than one type of wildcard in the same pattern. By combining symbols like %, _, and [], you can create search conditions that describe exactly what kind of text you want to match.

• % matches any number of characters.
• _ matches a single character.
• [] matches any one character from a list or range of characters.

When used together, these symbols let you write patterns that match text in very specific ways.

Example: Address Match

In the following example, we are retrieving customers whose address starts with H and ends with bad:

SELECT * FROM CUSTOMERS WHERE ADDRESS LIKE 'H%bad';

The output obtained is as follows:

Example: Starts with K and has l

The example below retrieves all names that start with K and contain the letter l later in the name:

SELECT * FROM CUSTOMERS WHERE NAME LIKE 'K_%l%';

We get the output as shown below: