【SQL LIKE Clause】Mastering Partial Matches! How to Use '%' and '_' Wildcards

A search function is essential for any website, isn't it? Countless times, you'll find yourself wanting to find data that isn't an exact match but a partial one, like "I want to find books containing the word 'SQL'" or "List all users whose names start with 'Suzuki'." Using = (equals) in a WHERE clause finds exact matches, but how do you handle "fuzzy searches" or "partial matches" like these?

The answer is SQL's LIKE clause. When combined with the WHERE clause, LIKE allows you to extract data based on a partial match of a string. What dramatically enhances its search capabilities are special symbols called wildcards. Just like a joker in a deck of cards, these symbols can stand in for any character, making your data searches more flexible and powerful.

This article will thoroughly explain everything from the basic usage of the LIKE clause, how to use the two main wildcards (% and _), to practical tips, all with copy-and-paste code. Now, let's explore the world of the LIKE clause on your journey to becoming a search master!

Preparation: Let's Set Up Our Book Data for Searching

To try out partial match searches, we first need some data to search through. This time, we'll create a simple books table to manage a list of books. We will intentionally register books with similar titles and titles containing special characters so we can test various search patterns.

-- Drop the books table if it exists (for repeatable testing)
DROP TABLE IF EXISTS books;

-- Create a new books table
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  price INTEGER
);

-- Insert initial data
INSERT INTO books (id, title, author, price) VALUES
(1, 'Getting Started with SQL', 'Yamada Taro', 2500),
(2, 'Practical SQL Data Analysis', 'Suzuki Ichiro', 3200),
(3, 'The Web Design Textbook', 'Sato Hanako', 2800),
(4, 'Fun Introduction to SQL', 'Yamada Taro', 2200),
(5, 'Website Production Guide', 'Sato Jiro', 3000),
(6, 'Marketing for 120% Growth', 'Tanaka Minoru', 1800),
(7, 'Getting Started with SQL, Part 2', 'Yamada Taro', 2600);

Now we're ready to try searching with various keywords.

Wildcard ①: `%` (Percent) - Zero or More Characters

The % is the most commonly used wildcard with the LIKE clause. It represents "any string of zero or more characters," regardless of length or type. In other words, think of it as the almighty, all-purpose card that can be anything.

Prefix Match: Finding data that "starts with..."

Using the pattern 'search_string%' allows you to find data that starts with the specified string.

Scenario: "I want to find books whose titles start with 'SQL'."

SELECT * FROM books WHERE title LIKE 'SQL%';

Result:

id | title                      | author      | price
---|----------------------------|-------------|-------
1  | Getting Started with SQL   | Yamada Taro | 2500
7  | Getting Started with SQL, Part 2 | Yamada Taro | 2600

It found the books with ID 1 and 7. 'Fun Introduction to SQL' and 'Practical SQL Data Analysis' were not included in the results because their titles do not start with "SQL".

Suffix Match: Finding data that "ends with..."

Using the pattern '%search_string' allows you to find data that ends with the specified string.

Scenario: "I want to find books by authors whose names end with 'Taro'."

SELECT * FROM books WHERE author LIKE '%Taro';

Result:

id | title                            | author      | price
---|----------------------------------|-------------|-------
1  | Getting Started with SQL         | Yamada Taro | 2500
4  | Fun Introduction to SQL          | Yamada Taro | 2200
7  | Getting Started with SQL, Part 2 | Yamada Taro | 2600

All books by "Yamada Taro" have been listed.

Substring Match (Contains): Finding data that "contains..."

This is the most versatile usage. By surrounding the search keyword with %, like '%search_string%', you can find all data that contains the specified string anywhere within it.

Scenario: "I want to find all books that contain the word 'SQL' in their title."

SELECT * FROM books WHERE title LIKE '%SQL%';

Result:

id | title                            | author        | price
---|----------------------------------|---------------|-------
1  | Getting Started with SQL         | Yamada Taro   | 2500
2  | Practical SQL Data Analysis      | Suzuki Ichiro | 3200
4  | Fun Introduction to SQL          | Yamada Taro   | 2200
7  | Getting Started with SQL, Part 2 | Yamada Taro   | 2600

This time, all books with "SQL" anywhere in the title are included in the search results. Queries like this are frequently used behind the scenes in website search features.

Wildcard ②: `_` (Underscore) - Any Single Character

The other important wildcard is the _ (underscore). While % was the "anything goes" wildcard for zero or more characters, the _ represents "exactly one character of any kind." The key difference is that the character count is fixed at exactly one.

This is useful for more precise searches, like distinguishing between "Sato" and "Saitou."

Scenario: "I want to find authors whose last name is 'Sato' and whose first name consists of four letters (e.g., Sato Jiro, Sato Hana)."

-- A pattern that matches 'Sato ' followed by any four characters
SELECT * FROM books WHERE author LIKE 'Sato ____';

Result:

id | title                   | author     | price
---|-------------------------|------------|-------
3  | The Web Design Textbook | Sato Hanako| 2800
5  | Website Production Guide| Sato Jiro  | 3000

By using four underscores, we specified "any four characters," which allowed us to find both "Sato Hanako" and "Sato Jiro". If we had used LIKE 'Sato _', it would only have matched authors with a one-letter first name (if any existed).

Application: Searching for '%' or '_' as Literal Characters (The `ESCAPE` Clause)

Now for a trickier problem. What if you want to find a book with "120%" in the title?

If you simply write WHERE title LIKE '%120%%', the first and last % symbols will be interpreted as wildcards, and you won't get the intended search results. For situations like this, where you want to search for the wildcard characters themselves as literal text, the ESCAPE clause comes into play.

The ESCAPE clause lets you define a special "escape character" (like `!`, `#`, or `\`) that tells the database to disable the special meaning of the wildcard that immediately follows it.

-- Define '!' as the escape character
SELECT * FROM books WHERE title LIKE '%120!%%' ESCAPE '!';

In this query, ESCAPE '!' specifies ! as the escape character. This means the part written as !% is interpreted as "this is not a wildcard, but a literal '%' character." As a result, only the book with ID 6, 'Marketing for 120% Growth', is correctly matched.

Commonly used escape characters include !, #, and `\` (backslash).

【Interactive Playground】Try Out Partial Match Searches in Your Browser!

It's time to turn knowledge into skill! Copy the entire HTML code below, save it as a file like sql_like_test.html, and open it in your browser. Your own personal SQL environment will launch, pre-loaded with the books table we've been using in this article.

Try changing the positions of % and _, or using different keywords, to see how the search results change. The path to becoming a search master is paved with trial and error!

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SQL LIKE Clause Online Playground</title>
  <style>
    body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif; line-height: 1.7; color: #333; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    h1 { color: #3498db; }
    textarea { width: 100%; height: 180px; font-family: "SF Mono", "Consolas", monospace; font-size: 16px; padding: 12px; border: 1px solid #ccc; border-radius: 6px; box-sizing: border-box; margin-bottom: 1rem; }
    button { background-color: #3498db; color: white; border: none; padding: 12px 22px; font-size: 16px; border-radius: 6px; cursor: pointer; transition: background-color 0.2s; }
    button:hover { background-color: #2980b9; }
    button:disabled { background-color: #bdc3c7; cursor: not-allowed; }
    #result-container { margin-top: 2rem; border: 1px solid #ddd; padding: 1rem; border-radius: 6px; background: #fdfdfd; min-height: 50px; }
    #error-message { color: #e74c3c; font-weight: bold; }
    table { border-collapse: collapse; width: 100%; margin-top: 1rem; }
    th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
    th { background-color: #f2f2f2; }
    tr:nth-child(even) { background-color: #f9f9f9; }
  </style>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.js"></script>
</head>
<body>

  <h1>Let's Try SQL!</h1>
  <p>Enter an SQL statement in the textarea below and click the "Execute" button. Try searching with different keywords!</p>

  <textarea id="sql-input">-- Let's find books with "Web" in the title
SELECT * FROM books WHERE title LIKE '%Web%';
  </textarea>
  
  <button id="execute-btn">Execute</button>
  
  <div id="result-container">
    <p id="error-message"></p>
    <div id="result-output"></div>
  </div>

  <script>
    const sqlInput = document.getElementById('sql-input');
    const executeBtn = document.getElementById('execute-btn');
    const errorMsg = document.getElementById('error-message');
    const resultOutput = document.getElementById('result-output');

    let db;

    async function initDb() {
      executeBtn.disabled = true;
      executeBtn.textContent = 'DB Initializing...';
      try {
        const SQL = await initSqlJs({
          locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/${file}`
        });
        db = new SQL.Database();
        
        const setupSql = `
          DROP TABLE IF EXISTS books;
          CREATE TABLE books (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            author TEXT NOT NULL,
            price INTEGER
          );
          INSERT INTO books (id, title, author, price) VALUES
          (1, 'Getting Started with SQL', 'Yamada Taro', 2500),
          (2, 'Practical SQL Data Analysis', 'Suzuki Ichiro', 3200),
          (3, 'The Web Design Textbook', 'Sato Hanako', 2800),
          (4, 'Fun Introduction to SQL', 'Yamada Taro', 2200),
          (5, 'Website Production Guide', 'Sato Jiro', 3000),
          (6, 'Marketing for 120% Growth', 'Tanaka Minoru', 1800),
          (7, 'Getting Started with SQL, Part 2', 'Yamada Taro', 2600);
        `;
        db.run(setupSql);
        
        executeBtn.disabled = false;
        executeBtn.textContent = 'Execute';
        resultOutput.innerHTML = '<p>Ready! Feel free to try out your own SQL queries.</p>';

      } catch (err) {
        errorMsg.textContent = 'Failed to initialize database: ' + err.message;
        console.error(err);
      }
    }

    function executeSql() {
      if (!db) return;
      
      const sql = sqlInput.value;
      errorMsg.textContent = '';
      resultOutput.innerHTML = '';

      try {
        const results = db.exec(sql);
        if (results.length === 0) {
          resultOutput.innerHTML = '<p>Query executed successfully, but no result set was returned.</p>';
          return;
        }
        
        results.forEach(result => {
          const table = document.createElement('table');
          const thead = document.createElement('thead');
          const tbody = document.createElement('tbody');
          
          const headerRow = document.createElement('tr');
          result.columns.forEach(colName => {
            const th = document.createElement('th');
            th.textContent = colName;
            headerRow.appendChild(th);
          });
          thead.appendChild(headerRow);
          
          result.values.forEach(row => {
            const bodyRow = document.createElement('tr');
            row.forEach(cellValue => {
              const td = document.createElement('td');
              td.textContent = cellValue === null ? 'NULL' : cellValue;
              bodyRow.appendChild(td);
            });
            tbody.appendChild(bodyRow);
          });
          
          table.appendChild(thead);
          table.appendChild(tbody);
          resultOutput.appendChild(table);
        });

      } catch (err) {
        errorMsg.textContent = 'SQL Error: ' + err.message;
        console.error(err);
      }
    }

    executeBtn.addEventListener('click', executeSql);
    
    initDb();
  </script>
</body>
</html>

Points to Watch Out For: Performance and Case Sensitivity

While the LIKE clause is very convenient, there are a few points to be aware of when using it in practice.

• Performance: Using % at the beginning of the search string, as in LIKE '%keyword' or LIKE '%keyword%', can significantly degrade search performance. This is because it prevents the database from using its indexes efficiently. On a huge table with millions of records, this can make searches very slow. Whenever possible, using a prefix search like LIKE 'keyword%' is the key to maintaining good performance.
• Case Sensitivity: Whether 'sql' and 'SQL' are treated as distinct depends on the database type and its settings (specifically, the "collation"). The default settings in MySQL, for example, do not distinguish between them, but other databases may be strictly case-sensitive. If you want to perform a case-insensitive search, the most reliable method is to use functions like LOWER() or UPPER() to convert both sides of the comparison to the same case, for example: WHERE LOWER(title) LIKE '%sql%'.

For high-speed searches on large amounts of text data, it is more common to use a more advanced feature called "Full-Text Search" rather than the LIKE clause. After mastering LIKE, learning about full-text search would be a great next step.

Conclusion

In this article, we've focused on the LIKE clause for performing partial match searches in SQL, centered around its powerful helpers, the wildcards.

• LIKE Clause: Used with the WHERE clause to perform "fuzzy" string searches.
• Wildcard %: Matches any string of zero or more characters. The three basic patterns are 'A%' (prefix match), '%A' (suffix match), and '%A%' (substring match).
• Wildcard _: Matches exactly one of any character. Used when you want to specify the number of characters.
• ESCAPE Clause: Used to search for the literal characters % or _ by defining an escape character to disable their wildcard functionality.
• Key Considerations: It's important to be aware of the performance issues with leading % and the differences in case sensitivity handling among databases.

Mastering the LIKE clause is an essential skill for providing user-friendly search functionality. Please use the interactive playground in this article to try out various patterns and get a feel for partial match searching. Your SQL skills have just leveled up again!