Sql SELECT WHERE field contains words

Turbo-charging performance with Full-Text Search

When your oh-so-humble dataset evolves into a hulking monster, LIKE with % wildcards might start panting. Here's when the full-text search swoops in. Databases like SQL Server, MySQL, and PostgreSQL have got your back with full-text search technologies, empowering you to execute more contemplative searches to heighten performance.

In SQL Server, using CONTAINS feels as smooth as butter:

SELECT * FROM table WHERE CONTAINS(column, 'word1 AND word2'); -- This is 'CONTAINS' country.


To get the real deal out of your database's full-text search attributes, remember to index your column.

Seeking and Nullifying

Sometimes, the search crusade requires case-insensitivity. Employ the sturdy knights, LOWER or UPPER functions, to make sure you're comparing apples with apples:

SELECT * FROM table WHERE LOWER(column) LIKE LOWER('%word%'); -- Why shout when we can whisper?


Beware of stealthy NULL values, which might sneakily omit rows:

SELECT * FROM table WHERE column IS NOT NULL AND column LIKE '%word%'; -- Null shall not pass!


Going Meta: Advanced Text Evaluation

Fragmentation and Conjunction

There are those times when you need to examine each word as an individual entity. You can slice and dice the words using stuff like a table-valued function or recursive CTEs. Then, individual scrutinization ensues:

SELECT * 
FROM table 
INNER JOIN (SELECT value FROM STRING_SPLIT(column, ' ')) as split_words 
ON column LIKE '%' + split_words.value + '%'; -- Bam! Words split faster than atoms!


Extracting Zest with Substring and Position Functions

Peel the layers of your string using substring extraction via SUBSTRING and locate the nucleus using position determination via CHARINDEX or LOCATE. Your queries shall know no bounds:

SELECT * FROM table WHERE CHARINDEX('word', column) > 0; -- Peekaboo, I see word!


The Need for Speed

Sometimes, the extra vroom comes from using temporary tables to store prior results or employing DISTINCT to dodge unwanted clones:

SELECT DISTINCT * FROM table WHERE column LIKE '%word%'; -- Duplicate records fear the Wrath of Distinct!


You can brush aside unwanted records with a LEFT JOIN paired with a NULL check in the join condition.