SQL SELECT WHERE field contains words ?

To query rows where a column contains specific words in SQL, you can use the LIKE operator for basic pattern matching or leverage full-text search (if supported by your database) for advanced scenarios. Below are detailed methods with examples:

1. Using LIKE for Basic Substring Matching

The LIKE operator checks if a column contains a substring, using % as a wildcard.

Example 1: Single Word

-- Find rows where the 'description' contains the word "apple"
SELECT *
FROM products
WHERE description LIKE '%apple%';

Example 2: Multiple Words (AND Logic)

-- Find rows where 'description' contains both "apple" AND "pie"
SELECT *
FROM products
WHERE description LIKE '%apple%'
  AND description LIKE '%pie%';

Example 3: Multiple Words (OR Logic)

-- Find rows where 'description' contains "apple" OR "banana"
SELECT *
FROM products
WHERE description LIKE '%apple%'
   OR description LIKE '%banana%';

Limitations:

• Case-insensitive in some databases (e.g., MySQL), case-sensitive in others (e.g., PostgreSQL with LIKE).
• Inefficient for large datasets (no index usage if wildcard % is at the start).

2. Using Full-Text Search (Advanced)

For efficient word-based searches, databases like PostgreSQL, MySQL, and SQL Server support full-text indexing.

Example in PostgreSQL:

-- Create a full-text index (if not already present)
CREATE INDEX idx_fts_description ON products USING GIN(to_tsvector('english', description));

-- Search for rows containing "apple" AND "pie"
SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'apple & pie');

Example in MySQL:

-- Enable full-text indexing on the column
ALTER TABLE products ADD FULLTEXT(description);

-- Search for "apple pie" (natural language mode)
SELECT *
FROM products
WHERE MATCH(description) AGAINST('apple pie');

Key Benefits:

• Handles stemming (e.g., “running” matches “run”).
• Supports boolean operators (+apple -pie = “apple” must exist, “pie” must not).
• Faster than LIKE for large datasets.

3. Using REGEXP or RLIKE for Regex Matching

For complex patterns, use regex (supported in PostgreSQL, MySQL, etc.).

Example:

-- Find rows where 'description' contains "apple" or "apples"
SELECT *
FROM products
WHERE description REGEXP 'apples?'; -- Matches "apple" or "apples"

4. Exact Word Matching

To match exact words (not substrings), add spaces around the term (cautiously).

Example:

-- Match "apple" as a standalone word (not "pineapple")
SELECT *
FROM products
WHERE description LIKE '% apple %'    -- Middle of string
   OR description LIKE 'apple %'      -- Start of string
   OR description LIKE '% apple';     -- End of string

5. Handling Case Sensitivity

Case-Insensitive Search in PostgreSQL:

SELECT *
FROM products
WHERE description ILIKE '%apple%'; -- ILIKE for case-insensitive

Case-Insensitive Search in SQL Server:

SELECT *
FROM products
WHERE description LIKE '%apple%' COLLATE SQL_Latin1_General_CP1_CI_AS;

Summary

Method	Use Case	Example
LIKE	Simple substring matching.	WHERE col LIKE '%word%'
Full-Text Search	Efficient word/boolean searches.	WHERE MATCH(col) AGAINST('word')
REGEXP/RLIKE	Complex pattern matching.	WHERE col REGEXP 'pattern'
Exact Word Matching	Match standalone words.	WHERE col LIKE '% word %'

Examples Across Databases

MySQL:

-- Find rows where 'title' contains "java" but not "script"
SELECT *
FROM books
WHERE MATCH(title) AGAINST('+java -script' IN BOOLEAN MODE);

SQL Server:

-- Full-text search for "coffee" AND "tea"
SELECT *
FROM products
WHERE CONTAINS(description, 'coffee AND tea');

PostgreSQL:

-- Search for "chocolate" OR "vanilla"
SELECT *
FROM desserts
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'chocolate | vanilla');

Best Practices

1. Indexing: Use full-text indexes for large datasets.
2. Collation: Specify collation for case sensitivity.
3. Parameterization: Escape user inputs to prevent SQL injection (e.g., '%' + userInput + '%').