Combining "LIKE" and "IN" in SQL Server

Advanced maneuvers: CASE and SUBQUERY

To magnify your pattern hunt, utilize CASE expressions and subqueries. This enables deeper conditional logic:

SELECT * FROM YourTable
WHERE YourColumn LIKE CASE WHEN ConditionColumn = 'A' THEN '%patternA%' 
                           WHEN ConditionColumn = 'B' THEN '%patternB%'
                           ELSE YourColumn 
                      END;


This lets you lay dynamite on pattern checks based on column values on the same row.

For herding a long list of patterns in the wild, mimic IN with a subquery:

SELECT * FROM YourTable
WHERE EXISTS (
    SELECT 1 
    FROM (VALUES ('%pattern1%'), ('%pattern2%')) AS Patterns(Pattern) -- This acts as your moles in the ground 
    WHERE YourColumn LIKE Patterns.Pattern
);


Take full advantage of table value constructors to round up your patterns like a shepherd with his sheep.

Performance tuning: Full-Text Search

In scenarios demanding heavy-duty search operations, SQL Server's Full-Text Search comes to your rescue. Full-text indexes can make your search ops fast like a car on an open highway, and precise like a surgeon's scalpel:

SELECT * FROM YourTable
WHERE CONTAINS(YourColumn, '"*pattern*"');


For maximum mileage, index column like a librarian with her books and steer clear of leading wildcards whenever possible.

Super-user tactics with UNION and JOIN

Certain tasks demand intricate maneuvers. Let's do some SQL acrobatics.

Bundle patterns under UNION:

For a barrage of complex patterns, split them into individual queries, bringing them together with UNION:

SELECT * FROM YourTable
WHERE YourColumn LIKE '%pattern1%'
UNION
SELECT * FROM YourTable
WHERE YourColumn LIKE '%pattern2%';


This keeps your SQL township clean and serene.

Choreograph a duet with JOIN:

At times, you're required to dance a JOIN based on a melody of matching patterns:

SELECT a.*, b.*
FROM TableA a
JOIN TableB b ON a.ColumnA LIKE '%' + b.ColumnB + '%';


Here, TableA and TableB tango elegantly based on their rhythmic pattern match.

Seamlessly sew patterns with Functions

Craft amazing functions, Wizards! They can encapsulate your logic in a cloaking spell of reusability and clarity.

A Table-Valued Function (TVF) returning matches might look like this:

CREATE FUNCTION dbo.MatchingPatterns (@Pattern NVARCHAR(100))
RETURNS TABLE AS RETURN (
    SELECT * FROM YourTable
    WHERE YourColumn LIKE '%' + @Pattern + '%'
);


Invoke this TVF in your grooves to wave your pattern-matching wand:

SELECT * FROM dbo.MatchingPatterns('interesting');
