[sql] How to count instances of character in SQL Column

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.

Here's what I used in Oracle SQL to see if someone was passing a correctly formatted phone number:

WHERE REPLACE(TRANSLATE('555-555-1212','0123456789-','00000000000'),'0','') IS NULL AND
LENGTH(REPLACE(TRANSLATE('555-555-1212','0123456789','0000000000'),'0','')) = 2

The first part checks to see if the phone number has only numbers and the hyphen and the second part checks to see that the phone number has only two hyphens.

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

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

If you want to count the number of instances of strings with more than a single character, you can either use the previous solution with regex, or this solution uses STRING_SPLIT, which I believe was introduced in SQL Server 2016. Also you’ll need compatibility level 130 and higher.

ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 130

.

--some data
DECLARE @table TABLE (col varchar(500))
INSERT INTO @table SELECT 'whaCHAR(10)teverCHAR(10)whateverCHAR(10)'
INSERT INTO @table SELECT 'whaCHAR(10)teverwhateverCHAR(10)'
INSERT INTO @table SELECT 'whaCHAR(10)teverCHAR(10)whateverCHAR(10)~'

--string to find
DECLARE @string varchar(100) = 'CHAR(10)'

--select
SELECT 
    col
  , (SELECT COUNT(*) - 1 FROM STRING_SPLIT (REPLACE(REPLACE(col, '~', ''), 'CHAR(10)', '~'), '~')) AS 'NumberOfBreaks'
FROM @table

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 will return number of occurance of N

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

try this

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

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

The easiest way is by using Oracle function:

SELECT REGEXP_COUNT(COLUMN_NAME,'CONDITION') FROM TABLE_NAME

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

Maybe something like this...

SELECT
    LEN(REPLACE(ColumnName, 'N', '')) as NumberOfYs
FROM
    SomeTable

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!

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:

Try this:

SELECT COUNT(DECODE(SUBSTR(UPPER(:main_string),rownum,LENGTH(:search_char)),UPPER(:search_char),1)) search_char_count
FROM DUAL
connect by rownum <= length(:main_string);

It determines the number of single character occurrences as well as the sub-string occurrences in main string.

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