PostgreSQL LIKE

LIKE Wildcards

WHERE name LIKE 'A%'
-- Matches: Alice, Andrew, Amy

WHERE code LIKE 'A_C'
-- Matches: ABC, AXC, A5C

WHERE email LIKE 'admin%'
-- Matches: [email protected]

WHERE email LIKE '%@gmail.com'
-- Matches: [email protected]

🔹 Basic LIKE Pattern

The LIKE operator uses the percent sign (%) as a wildcard that matches any sequence of characters, including zero characters. It's case-sensitive by default. Use it to search for text that starts with, ends with, or contains specific patterns in your database columns.

-- Sample data: customers table
-- customer_id | name
-- 1           | John Smith
-- 2           | Jane Doe
-- 3           | James Wilson
-- 4           | Sarah Johnson

-- Find all names starting with 'J'
SELECT name FROM customers
WHERE name LIKE 'J%';

🔹 LIKE with Ending Pattern

Place the percent sign at the beginning to match text ending with a specific pattern. This is useful for finding email addresses from certain domains, file extensions, or any text with common suffixes. The pattern matches everything before the specified ending characters.

-- Find all Gmail email addresses
SELECT email FROM users
WHERE email LIKE '%@gmail.com';

🔹 LIKE with Contains Pattern

Use percent signs on both sides to find text containing a pattern anywhere within the string. This searches for the pattern at the beginning, middle, or end of the text. Perfect for general searches where you don't know the exact position of your search term.

-- Find products containing 'phone' anywhere in name
SELECT product_name FROM products
WHERE product_name LIKE '%phone%';

🔹 LIKE with Underscore Wildcard

The underscore (_) wildcard matches exactly one character at that position. Unlike percent which matches any number of characters, underscore is precise. Use it when you know the length of the text and want to match specific positions, like product codes or formatted IDs.

-- Find 3-letter codes starting with 'A' and ending with 'C'
SELECT code FROM products
WHERE code LIKE 'A_C';

🔹 Combining Wildcards

You can combine percent and underscore wildcards in the same pattern for complex searches. This gives you precise control over pattern matching, allowing you to specify exact character positions while keeping other parts flexible. Useful for structured data like phone numbers or product codes.

-- Find phone numbers with area code 555
-- Format: (555) XXX-XXXX
SELECT phone FROM contacts
WHERE phone LIKE '(555) ___-____';

🔹 NOT LIKE Operator

Use NOT LIKE to find records that don't match a pattern. This is the opposite of LIKE and helps exclude certain patterns from results. Useful for filtering out test data, specific domains, or unwanted patterns. Combine with wildcards for flexible exclusion criteria.

-- Find emails NOT from Gmail
SELECT email FROM users
WHERE email NOT LIKE '%@gmail.com';

🔹 Case-Insensitive LIKE (ILIKE)

PostgreSQL provides ILIKE for case-insensitive pattern matching. Unlike LIKE which is case-sensitive, ILIKE matches patterns regardless of letter case. This is helpful for user searches where you don't want to worry about capitalization. ILIKE is a PostgreSQL-specific extension, not standard SQL.

-- Case-insensitive search for 'john'
SELECT name FROM customers
WHERE name ILIKE '%john%';