How to count instances of character in SQL Column
DECLARE @StringToFind VARCHAR(100) = "Text To Count"
SELECT (LEN([Field To Search]) - LEN(REPLACE([Field To Search],@StringToFind,'')))/COALESCE(NULLIF(LEN(@StringToFind), 0), 1) --protect division from zero
FROM [Table To Search]
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
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', ''))
In SQL Server:
SELECT LEN(REPLACE(myColumn, 'N', ''))
FROM ...