SQL - Wildcards Characters

SQL Wildcard Characters

SQL Wildcardsare 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, theLIKEoperator is case-sensitive, while in others it is not.

Common SQL Wildcard Characters

Following are the most commonly used wildcard characters in SQL:

S.No.Wildcard & Description1The percent sign (%)Represents zero, one, or multiple characters.2The underscore (_)Represents a single character.3Square brackets ([])Matches any single character within the specified range or set (SQL Server).4Caret (^)Matches any single characternotin the specified range or set (SQL Server).5Hyphen (-)Specifies a range of characters (used inside brackets).6Curly braces ({})Used in some databases/regex-like extensions to specify the number of occurrences.

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:

S.No.DatabaseSupported WildcardsNotes / Extras1MySQL%,_SupportsREGEXPfor advanced patterns (uses POSIX regular expressions).2PostgreSQL%,Also supportsSIMILAR TO,,*for regex.3SQL Server%,,[],[^ ]Supports richer wildcard options with sets and ranges.4Oracle%,_SupportsREGEXP_LIKEfor advanced regex matching.5MS Access*,?,[],!Uses Access-specific syntax.6SQLite%,_Also supportsGLOB(Unix shell–style wildcards:*,?,[]).7DB2%,_Supports regular expressions throughREGEXP_LIKEin newer versions.

SQL Wildcard Syntax

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

SQL CODE

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:

S.No.Statement & Description1WHERE SALARY LIKE ‘200%‘Finds any values that start with 200.2WHERE SALARY LIKE ‘%200%‘Finds any values that have 200 in any position.3WHERE SALARY LIKE ‘00%‘Finds any values that have 00 in the second and third positions.4WHERE SALARY LIKE ‘2%_%‘Finds any values that start with 2 and are at least 3 characters in length.5WHERE SALARY LIKE ‘%2’Finds any values that end with 2.6WHERE SALARY LIKE ‘_2%3’Finds any values that have a 2 in the second position and end with a 3.7WHERE SALARY LIKE ‘2___3’Finds any values in a five-digit number that start with 2 and end with 3.

Now, let us go through practical examples of using wildcards with theLIKEoperator. 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 aLIKEcondition 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 asAmit,Arun,Apple).
’%n’matches any string that ends with the letter n (such asRajan,Rohan).
’%sh%‘matches any string that contains the lettersshanywhere (such asRamesh,Shyam,Krishna).

Example: Starts with K

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

SQL CODE

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:

SQL CODE

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:

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.002Khilan25Delhi1500.003Kaushik23Kota2000.004Chaitali25Mumbai6500.005Hardik27Bhopal8500.006Komal22Hyderabad4500.007Muffy24Indore10000.00

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

SQL CODE

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

The output obtained is as follows:

IDNAMEAGEADDRESSSALARY2Khilan25Delhi1500.003Kaushik23Kota2000.006Komal22Hyderabad4500.00

Example: Ends with h

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

SQL CODE

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

We get the output as shown below:

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.00

Example: Contains bad

Following is an example to search for customers who havebadanywhere in their address:

SQL CODE

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

The output obtained is as follows:

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.006Komal22Hyderabad4500.00

Using the _ Wildcard

The underscore (_) wildcard is used in SQL to representexactly 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 witha(such asKa,Ma).
’A_c’matches any three-character string starting withAand ending withc(such asAcc,Abc).
’_00%‘matches any number that has00in the second and third positions (such as2000,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:

SQL CODE

SELECT * FROM CUSTOMERS WHERE NAME LIKE '_aushik';

We get the output as shown below:

IDNAMEAGEADDRESSSALARY3Kaushik23Kota2000.00

Example: Salary pattern

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

SQL CODE

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

The output obtained is as follows:

IDNAMEAGEADDRESSSALARY2Khilan25Delhi1500.00

Using [] Wildcards (SQL Server / MS Access)

The square brackets ([]) wildcard is used in SQL Server and MS Access to matchany single characterfrom 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 eitherAorK(such asArun,Kaushik).
’[0-9]%‘matches any string starting with a digit between 0 and 9 (such as2000,9thStreet).
’M[aeiou]%‘matches any string starting withMfollowed by a vowel (such asManoj,Meera,Mukesh).

Example: Starts with R or K

In the example below, we are retrieving all customers whose names start with eitherRorK:

SQL CODE

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

The output obtained is as follows:

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.002Khilan25Delhi1500.003Kaushik23Kota2000.006Komal22Hyderabad4500.00

Example: Range A-M

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

SQL CODE

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

We get the output as shown below:

IDNAMEAGEADDRESSSALARY4Chaitali25Mumbai6500.005Hardik27Bhopal8500.003Kaushik23Kota2000.006Komal22Hyderabad4500.002Khilan25Delhi1500.007Muffy24Indore10000.00

Using ^ Wildcard (SQL Server)

The caret (^) wildcard is used inside square brackets in SQL Server to representany 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 doesnotstart with the letterA(such asKaushik,Ramesh, but notArun).
’[^0-9]%‘matches any string that doesnotstart with a digit (such asKhilan,Muffy).
’M[^aeiou]%‘matches any string starting withMfollowed by a consonant (such asManojwould not match, butMritunwould match).

Example: Excluding K

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

SQL CODE

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

The output obtained is as follows:

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.004Chaitali25Mumbai6500.005Hardik27Bhopal8500.007Muffy24Indore10000.00

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 withbad:

SQL CODE

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

The output obtained is as follows:

IDNAMEAGEADDRESSSALARY6Komal22Hyderabad4500.00

Example: Starts with K and has l

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

SQL CODE

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

We get the output as shown below:

IDNAMEAGEADDRESSSALARY2Khilan25Delhi1500.006Komal22Hyderabad4500.00