How to use LIKE with column name

sql

pattern-matching

sql-server

mysql

byAlex Kataev·Dec 15, 2024

To combine the SQL LIKE operator with column names, use this structure:

SELECT *
FROM your_table
WHERE column1 LIKE CONCAT('%', column2, '%');

In this query, CONCAT unites the wildcard % with the values in column2, forming a dynamic search pattern for column1. These values will be any where column2 is contained within them. Change CONCAT according to the syntaxes of your SQL dialect (For example, use + for SQL Server).

Basic patterns and wildcards

By pairing LIKE with % (wildcard), a tremendous amount of pattern-matching power is unleashed:

In SQL Server, use + operator to combine patterns with column values.

-- When tables talk to each other
SELECT *
FROM table1
WHERE column1 LIKE '%' + column2 + '%';

MySQL users, you can't simply add patterns and column values. Don't fret, use CONCAT().

-- MySQL's way of saying "Can we be friends?"
SELECT *
FROM table1
WHERE column1 LIKE CONCAT('%', column2, '%');

Type Compatibility: Be sure that you're comparing apple to apple (strings with strings). For oranges (numerical types), you'll need to convert them explicitly.

Improvising with dynamic patterns

Situations call for dynamic patterns in your LIKE statements. Let's tackle this:

Dynamic Wildcards: Variables can hold the wildcard characters, resulting in dynamic and customizable LIKE patterns.

-- SQL's version of "Expect the unexpected"
DECLARE @pattern VARCHAR(100) = '%' + @searchTerm + '%';
SELECT *
FROM table1
WHERE column1 LIKE @pattern;

Variable Encapsulation: Consider your variables as a bottle of fine wine. Enclose them securely within single quotes in your LIKE statement, especially if they're part of the pattern.
Flaw Checking: Test your LIKE patterns against sample data to validate the results. Remember, an untested code is a potential bug hive.

Performance considerations with LIKE

LIKE operator can take a heavy toll on performance, especially with leading wildcards. Some tips to break-even:

Index Usage: Remember, leading wildcard prevents the use of indexes. So use it sparingly.
Pattern Refinement: When feasible, tailor your LIKE pattern as precisely as required. Lesser the dataset, smoother the query.

Wildcards literally explained

LIKE operator provides two wildcards, % and _, for pattern matching:

Percent (%): Matches any sequence of characters, even zero.
Underscore (_): Matches exactly one character.

Using these symbols, LIKE can become a powerful tool for pattern matching:

-- Celebrate the power of wildcards
SELECT * FROM employees WHERE name LIKE 'A%';  -- Starts with 'A'  
SELECT * FROM employees WHERE name LIKE 'A_';  -- 'A' plus one character

Pattern matching: Advanced level

Things can get a bit complex with LIKE. But hey, who doesn't enjoy a good challenge:

Case Sensitivity: LIKE is case-insensitive in SQL Server or MySQL, but case-sensitive in PostgreSQL. Understand your database's behavior and use UPPER() or LOWER() if needed.
```
-- No room for assumptions!
SELECT * FROM table1 WHERE LOWER(column1) LIKE LOWER('%SomeValue%');
```

Escape Characters: Need to match the wildcard character itself? Use the escape clause.

-- % wants to be found too!
SELECT * FROM table1 WHERE column1 LIKE '%25\%%' ESCAPE '\';

Leading wildcard optimization: Often using leading wildcards (%value)? Consider Full-Text search alternatives. They perform better on such patterns.