SQL Wildcards

 SQL wildcards are special characters used with the SQL LIKE operator to perform pattern matching in a query. They are particularly useful when searching for rows in a table where the exact value is unknown or you want to filter rows based on a specific pattern.

Common SQL Wildcards

1. Percent (%):

   • Represents zero, one, or multiple characters.
   • Example:

     SELECT * FROM Employees WHERE FirstName LIKE 'A%';
     

     Finds all employees whose first name starts with "A" (e.g., "Alice", "Adam").

2. Underscore (_):

   • Represents a single character.
   • Example:

     SELECT * FROM Employees WHERE LastName LIKE 'J_nes';
     

     Finds all employees with last names like "Jones" or "Janes."

3. Square Brackets ([]):

   • Specifies a set or range of characters.
   • Example:

     SELECT * FROM Employees WHERE LastName LIKE '[A-C]%';
     

     Finds all employees whose last name starts with "A", "B", or "C".

4. Caret (^) or Exclamation Mark (!):

   • Used within square brackets to indicate "not."
   • Example:

     SELECT * FROM Employees WHERE FirstName LIKE '[^A-E]%';
     

     Finds all employees whose first name does not start with "A" through "E."

5. Hyphen (-):

   • Defines a range of characters within square brackets.
   • Example:

     SELECT * FROM Products WHERE ProductCode LIKE 'P[1-5]%';
     

     Finds all products with codes starting with "P" followed by a digit from 1 to 5.

6. Escape Character:

   • If you need to search for literal % or _, use an escape character.
   • Example:

     SELECT * FROM Files WHERE FileName LIKE '50\%_discount' ESCAPE '\';
     

     Finds files named "50%_discount."

Usage Considerations

• Wildcards work with the LIKE operator in SQL.
• They are case-insensitive in many database systems, such as MySQL, but case-sensitive in others, like PostgreSQL.
• Using wildcards in queries can slow down performance, especially with large datasets, because it prevents the database from using indexes effectively.