Count the number of occurrences of a string in a VARCHAR field?

Handling "clingy" substrings

Sometimes, substrings love being close — they overlap. Our above fast answer can, like a bad DJ, skip these overlaps. This SQL-specific problem might need a recursive CTE (Common Table Expression) or a stored procedure to resolve.

Exploring LENGTH() and REPLACE()

SQL, like a toolbox, gives us REPLACE() to remove strings and LENGTH() to measure. For performance-critical databases, reusing these tools repeatedly might leave your server sweating. Your rescue could be indexing or precomputing these values in a nightly batch.

Flirting with SQL dialects

Like how saying "howdy" instead of "hello" changes with location, SQL dialects have alternative functions. For instance, SQL Server uses LEN() instead of CHAR_LENGTH().

-- It's like a diet for your substrings
SELECT (LEN(column_name) - LEN(REPLACE(column_name, 'substring', ''))) / LEN('substring') AS occurrence_count
FROM table_name;


Knowledge of your database system and its functions will help you avoid tripping over syntax differences.

Polishing the results with ROUND()

Do you want integer results? Inviting ROUND() to your SQL party can help you get the occurrence_count rounded up.

SELECT ROUND((CHAR_LENGTH(column_name) - CHAR_LENGTH(REPLACE(column_name, 'substring', ''))) / CHAR_LENGTH('substring')) AS occurrence_count
FROM table_name;


This step ensures the output is a party-perfect integer.

Making SQL work smarter

Deja vu with repeated SQL commands? Create a dedicated SQL function to rescue your keystrokes:

DELIMITER //
CREATE FUNCTION CountSubstringOccurrences(column_name VARCHAR(255), substring VARCHAR(255)) RETURNS INT DETERMINISTIC
BEGIN
    -- SQL function at your service, no tips needed!
    RETURN (CHAR_LENGTH(column_name) - CHAR_LENGTH(REPLACE(column_name, substring, ''))) / CHAR_LENGTH(substring);
END //
DELIMITER ;


This little wizard will appear when summoned in SQL queries, doing the hard work behind the scenes.

Taming the special characters

Watch out for special characters like % or _. These can have a special meaning to the REPLACE function. Escaping them can help you play nice and get accurate results.

Don't neglect your testing

A wise coder tests their queries before letting them loose in the wild. Sure, SQL Fiddle works for quick checks, but testing your commands in a native environment offers real, actionable insights.