How to count instances of character in SQL Column

This will return number of occurance of N

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

Below solution help to find out no of character present from a string with a limitation:

1) using SELECT LEN(REPLACE(myColumn, 'N', '')), but limitation and wrong output in below condition:

SELECT LEN(REPLACE('YYNYNYYNNNYYNY', 'N', ''));
--8 --Correct

SELECT LEN(REPLACE('123a123a12', 'a', ''));
--8 --Wrong

SELECT LEN(REPLACE('123a123a12', '1', ''));
--7 --Wrong

2) Try with below solution for correct output:

• Create a function and also modify as per requirement.
• And call function as per below

select dbo.vj_count_char_from_string('123a123a12','2');
--2 --Correct

select dbo.vj_count_char_from_string('123a123a12','a');
--2 --Correct

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      VIKRAM JAIN
-- Create date: 20 MARCH 2019
-- Description: Count char from string
-- =============================================
create FUNCTION vj_count_char_from_string
(
    @string nvarchar(500),
    @find_char char(1)  
)
RETURNS integer
AS
BEGIN
    -- Declare the return variable here
    DECLARE @total_char int; DECLARE @position INT;
    SET @total_char=0; set @position = 1;

    -- Add the T-SQL statements to compute the return value here
    if LEN(@string)>0
    BEGIN
        WHILE @position <= LEN(@string) -1
        BEGIN
            if SUBSTRING(@string, @position, 1) = @find_char
            BEGIN
                SET @total_char+= 1;
            END
            SET @position+= 1;
        END
    END;

    -- Return the result of the function
    RETURN @total_char;

END
GO

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!

If you need to count the char in a string with more then 2 kinds of chars, you can use instead of 'n' - some operator or regex of the chars accept the char you need.

SELECT LEN(REPLACE(col, 'N', ''))

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

DECLARE @char NVARCHAR(50);
DECLARE @counter INT = 0;
DECLARE @i INT = 1;
DECLARE @search NVARCHAR(10) = 'Y'
    SET @char = N'YYNYNYYNNNYYNY';
    WHILE @i <= LEN(@char)
     BEGIN
        IF SUBSTRING(@char, @i, 1) = @search
            SET @counter += 1;

        SET @i += 1;
     END;

     SELECT @counter;