How to count instances of character in SQL Column

sql string tsql count symbols
cindi picture cindi · Dec 7, 2009 · Viewed 300.7k times · Source

I have an sql column that is a string of 100 'Y' or 'N' characters. For example:

YYNYNYYNNNYYNY...

What is the easiest way to get the count of all 'Y' symbols in each row.

Answer

nickf picture nickf · Dec 7, 2009

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', ''))

Related questions

How do I remove the first characters of a specific column in a table?

In SQL, how can I remove the first 4 characters of values of a specific column in a table? Column name is Student Code and an example value is ABCD123Stu1231. I want to remove first 4 chars from my table for …

Read More
Better techniques for trimming leading zeros in SQL Server?

I've been using this for some time: SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to …

Read More
IndexOf function in T-SQL

Given an email address column, I need to find the position of the @ sign for substringing. What is the indexof function, for strings in T-SQL? Looking for something that returns the position of a substring within a string. in C# …

Read More