T-sql and the WHERE LIKE %Parameter% clause

sql

prompt-engineering

best-practices

performance

byAnton Shumikhin·Sep 12, 2024

To perform pattern matching using LIKE in T-SQL, remember to encapsulate your search term with the percent signs, LIKE '%' + @SearchTerm + '%'. This format will fetch rows where your specific column contains the search term.

-- Beware of Batman! He likes to hide in shadows and comments.
SELECT * FROM YourTable WHERE YourColumn LIKE '%' + @SearchTerm + '%';

Warning: use parameters cautiously to avoid performance drops; indexes might be disregarded with leading wildcards.

Painless concatenation and efficient parameterization

Single-quoted disaster

Always avoid the single quotes (' ') around parameters when you're concatening. Instead, see the CONCAT function or use explicit concatenation as your trusty sidekicks.

-- Here is Batman's sidekick, Robin, coming to rescue!
SELECT * FROM Employees WHERE LastName LIKE '%' + @LastName + '%';

-- And here is Alfred with the CONCAT utility-belt!
SELECT * FROM Employees WHERE LastName LIKE CONCAT('%', @LastName, '%');

The wild(wild) wildcards

While testing may seem like a bother, always test your queries with diverse what-if-scenarios to confirm they deliver the desired results. Let your testing routine be as unpredictable as the Joker and as thorough as Batman's preparation.

Indexation, the silent hero

Optimizations are vital for super-speed searches. However, don't overlook the limits of '%Parameter%' style searches due to wildcard peculiarities.

Visualising the query shenanigans

Let's examine the mystery behind WHERE LIKE %Parameter% in T-SQL:

Think of your T-SQL query as Alfred, searching through rows of books (📚📚📚) in Batman's gigantic Bat-library, looking for any books that contain 'Parameter' anywhere in them.

Clever tricks for smart queries

Speedy Gonzales or Slowpoke Rodriguez?

Beware, using LIKE '%' + @Parameter + '%' can be as slow as a turtle on a coffee break due to possible full table scans. Large datasets, be warned.

'Bat'tle against bad performance

On a brighter note, %Parameter style can make use of indexes. Queries that search for @Parameter + '%' can leverage properly indexed columns, racing faster than the Batmobile.

Wildcard escape artist

Have a percentage sign in your search term that you don't want as a wildcard? No problem! Just disguise it with an escape:

-- Now you see me, now you don't
SELECT * FROM Products WHERE Name LIKE '%' + REPLACE(@SearchTerm, '%', '\%') + '%' ESCAPE '\';

Case-conundrum?

Does case sensitivity make your brain ache? Here’s a handy trick. To do case-insensitive searches on a case-sensitive column, use:

-- Just like Batman hides his identity, we hide the case-sensitivity here
SELECT * FROM Articles WHERE Title COLLATE Latin1_General_CI_AS LIKE '%' + @Keyword + '%';