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.

Answer 1

try this

declare @v varchar(250) = 'test.a,1  ;hheuw-20;'
-- LF   ;
select len(replace(@v,';','11'))-len(@v)

Answer 2

You can also Try This

-- DECLARE field because your table type may be text
DECLARE @mmRxClaim nvarchar(MAX) 

-- Getting Value from table
SELECT top (1) @mmRxClaim = mRxClaim FROM RxClaim WHERE rxclaimid_PK =362

-- Main String Value
SELECT @mmRxClaim AS MainStringValue

-- Count Multiple Character for this number of space will be number of character
SELECT LEN(@mmRxClaim) - LEN(REPLACE(@mmRxClaim, 'GS', ' ')) AS CountMultipleCharacter

-- Count Single Character for this number of space will be one
SELECT LEN(@mmRxClaim) - LEN(REPLACE(@mmRxClaim, 'G', '')) AS CountSingleCharacter

Output:

Answer 3

for example to calculate the count instances of character (a) in SQL Column ->name is column name '' ( and in doblequote's is empty i am replace a with nocharecter @'')

select len(name)- len(replace(name,'a','')) from TESTING

select len('YYNYNYYNNNYYNY')- len(replace('YYNYNYYNNNYYNY','y',''))

Answer 4

The second answer provided by nickf is very clever. However, it only works for a character length of the target sub-string of 1 and ignores spaces. Specifically, there were two leading spaces in my data, which SQL helpfully removes (I didn't know this) when all the characters on the right-hand-side are removed. Which meant that

" John Smith"

generated 12 using Nickf's method, whereas:

" Joe Bloggs, John Smith"

generated 10, and

" Joe Bloggs, John Smith, John Smith"

Generated 20.

I've therefore modified the solution slightly to the following, which works for me:

Select (len(replace(Sales_Reps,' ',''))- len(replace((replace(Sales_Reps, ' ','')),'JohnSmith','')))/9 as Count_JS

I'm sure someone can think of a better way of doing it!

Answer 5

The easiest way is by using Oracle function:

SELECT REGEXP_COUNT(COLUMN_NAME,'CONDITION') FROM TABLE_NAME

Answer 6

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