SQL Wildcards

Best SQL Wildcards in 2023

by

The SQL Wildcards

The sql wildcards are special characters used in conjunction with the LIKE operator to perform pattern matching in text searches. Wildcards allow you to search for strings that match a specific pattern rather than an exact value.

There are three commonly used wildcards in SQL.

  1. % (Percent Sign): This sql wildcard represents zero or more characters.
  2. _ (Underscore): This sql wildcard represents a single character.
  3. [character_list]: This sql wildcard allows you to specify a character list to match any single character from that list.
  4. % Wildcard (Percentage Sign):

The % wildcard matches any sequence of characters (including no characters) in a column. It’s often used to search for partial matches.

Examples of sql wildcards

  1. % (Percent Sign):

Example 1: Matching All Names That End with “son”
SQL> SELECT * FROM Employees
WHERE LastName LIKE ‘%son’;

Example 2: Finding All Email Addresses from a Specific Domain
SQL> SELECT * FROM Customers
WHERE Email LIKE ‘%@example.com’;

  1. _ Wildcard (Underscore):

The _ wildcard matches any single character in a column. It’s useful when you want to specify a specific character at a certain position in the pattern.

Example 1: Finding Names with ‘a’ as the Second Letter
SQL> SELECT * FROM Employees
WHERE LastName LIKE ‘_a%’;

Example 2: Searching for Four-Letter Words Starting with “T”
SQL> SELECT * FROM Words
WHERE Word LIKE ‘T___’;

Combining sql wildcards:
Example: Finding Names Containing “an”
SQL> SELECT * FROM Employees
WHERE LastName LIKE ‘%an%’;

Escaping sql wildcards:
SQL> SELECT * FROM Products
WHERE ProductName LIKE ’20\% Off%’;

  1. [character_list]:

To find names with “e” or “i” as the second letter, you can use [character_list]:

SELECT * FROM employees
WHERE name LIKE ‘_[ei]%’;

This query will return names where the second letter is either “e” or “i.”

You can also use a range within the character list. To find names where the second letter is a vowel (a, e, i, o, or u), you can use [a-e]:

SELECT * FROM employees
WHERE name LIKE ‘_[a-e]%’;

This query will return names where the second letter is any vowel (a, e, i, o, or u).

This query will match all rows where the “ProductName” starts with ‘20% Off’.

The sql wildcards are useful tools for flexible text searching and pattern matching inside columns. They make it simpler to access pertinent data from your database since they enable you to locate data that only loosely resembles a certain pattern.

Leave a Comment