Function to count occurrences of a substring

There is no trivial way to count the occurrences of a substring in a string. If you try searching Google, you'll see that many people asked how to that over the years. Generally, people suggest using expressions like this, which is terrible and painful:

CHAR_LENGTH(haystack) - CHAR_LENGTH(REPLACE(haystack, needle, '')) / CHAR_LENGTH(needle)

Surprisingly, from a quick research, no DBMS seems to have a SUBSTR_COUNT() or equivalent function. Some have REGEXP_COUNT(), but I suggest implementing a simple function for simple cases instead.

Some practical use cases for SUBSTR_COUNT() include counting the elements of a CSV and writing a CHECK constraint to validate strings in a particular format.