How to count instances of character in SQL Column
This snippet works in the specific situation where you have a boolean: it answers "how many non-Ns are there?".
SELECT LEN(REPLACE(col, 'N', ''))If, in a different situation, you were actually trying to count the occurrences of a certain character (for example 'Y') in any given string, use this:
SELECT LEN(col) - LEN(REPLACE(col, 'Y', ''))
This gave me accurate results every time...
This is in my Stripes field...
Yellow, Yellow, Yellow, Yellow, Yellow, Yellow, Black, Yellow, Yellow, Red, Yellow, Yellow, Yellow, Black
- 11 Yellows
- 2 Black
- 1 Red
SELECT (LEN(Stripes) - LEN(REPLACE(Stripes, 'Red', ''))) / LEN('Red') FROM t_Contacts