How to count instances of character in SQL Column

Question

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.

Solution 1

In SQL Server:

SELECT LEN(REPLACE(myColumn, 'N', '')) 
FROM ...

Solution 2

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

Solution 3

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

Solution 4

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]

Solution 5

This will return number of occurance of N

select ColumnName, LEN(ColumnName)- LEN(REPLACE(ColumnName, 'N', '')) from Table

View more solutions

Comments

[Vincent Ramdhanie]

Can you specify the platform? MySQL, MSSQl, Oracle?

[JGFMK]

Yes - with Oracle it seems you need length - not len

[Tom H]

Just be aware that if there are more than "N" or "Y" in the string then this could be inaccurate. See nickf's solution for a more robust method.

[Steve Bennett]

The second one is the best answer here. All the rest rely on the peculiar situation of the string containing only two different characters.

[Luke]

Just a note: in T-SQL, you'll need to use LEN rather than LENGTH.

[Kevin Heidt]

+1 This enhances the second suggestion by @nickf so that it will actually tell you the number of instances of a string even if the string you're looking for is more than 1 character

[Ben]

What does this question have to do with phone numbers? It's also asking for a T-SQL solution...

[domenicr]

@nickf SQL len function trims trailing spaces so if you were looking for how many occurrences of a space within a string let's say 'Hello ' you would get 0. Easiest way would be to add a trailing character to the string before and adjust len like so. SELECT LEN(col + '~') - LEN(REPLACE(col, 'Y', '') + '~')

[StevenWhite]

If you're concerned about trailing spaces, use the DATALENGTH function instead.

[Jamie Kitson]

@domenicr's edit has broken this answer and my edit was rejected. The division should be by LEN(@StringToFind).

[domenicr]

@jamiek apologies I have submitted corrected code, but don't know why your edit was rejected.

[domenicr]

@StevenWhite DATALENGTH returns number of bytes used. So NVARCHAR will be doubled.

[Jamie Kitson]

@domenicr You should revert to the original code, your edit complicates the code to no purpose, @StringToFind is never going to be null or empty.

[domenicr]

@JamieKitson I see otherwise. Checking for a division by zero is a principle of best practices. Also, counting the number of spaces in Field To Search would get a division by zero because Len(' ') returns zero.

[Jamie Kitson]

@domenicr So switch to DATALENGTH. KISS.

[domenicr]

@JamieKitson DATALENGTH returns number of bytes used. So this code will break if you use UNICODE characters. Looks simple to me.

[Gregory R.]

In order to make this work case insensitive, lowercase the col and value, in this case being 'Y'

[Simas Joneliunas]

Hi, it would be great if you could explain what your code does. It does make your answer a lot better and easier to understand for the rest of us!

[ashkanyo]

Hi, I think it counts a specific character in a string with a loop. if the current character equals the character we want to search it adds one to the counter until the loop ends. the example in the question was to count the Ys in the string which is 8.