By cindi


2009-12-07 14:56:06 8 Comments

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.

16 comments

@Ron Sell 2012-11-16 00:30:19

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

@Faisal Shahzad 2019-08-27 11:57:40

This will return number of occurance of N

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

@user11116003 2019-05-02 06:06:07

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

@jainvikram444 2019-03-20 09:43:50

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

@David Hedlund 2009-12-07 14:57:48

In SQL Server:

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

@Tom H 2009-12-07 15:28:10

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.

@nickf 2009-12-07 14:58:21

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

@Steve Bennett 2013-01-17 01:05:43

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 2013-07-30 15:51:50

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

@jasonk 2014-01-27 19:48:20

@Luke - I edited nickf's original to fix it.

@domenicr 2015-09-23 18:10:56

@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 2015-11-10 17:02:00

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

@domenicr 2015-11-20 20:54:35

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

@gady RajinikanthB 2017-10-11 12:33:18

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

@atik sarker 2017-04-20 11:51:52

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:

enter image description here

@Statsanalyst 2016-10-14 17:26:01

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!

@user3469285 2016-09-09 17:24:49

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

@Himanshu Tiwari 2016-07-11 12:57:02

Try This. It determines the no. of single character occurrences as well as the sub-string occurrences in main string.

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);

@Mayuresh Bhabal 2016-04-27 10:42:34

The easiest way is by using Oracle function:

SELECT REGEXP_COUNT(COLUMN_NAME,'CONDITION') FROM TABLE_NAME

@Aaron Dake 2014-02-17 13:54:41

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]

@Kevin Heidt 2014-03-06 15:29:33

+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

@Jamie Kitson 2015-11-19 12:34:00

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

@domenicr 2015-11-20 20:39:33

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

@Jamie Kitson 2015-11-25 12:43:13

@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 2015-11-25 19:11:48

@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 2015-11-26 10:14:03

@domenicr So switch to DATALENGTH. KISS.

@domenicr 2015-11-26 14:47:40

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

@user5546076 2015-11-10 10:06:31

try this

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

@TonyK 2015-02-05 20:39:12

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.

@Ben 2015-02-05 20:59:08

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

@Jason Punyon 2009-12-07 14:58:19

Maybe something like this...

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

Related Questions

Sponsored Content

39 Answered Questions

18 Answered Questions

[SOLVED] Reference — What does this symbol mean in PHP?

3 Answered Questions

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

76 Answered Questions

[SOLVED] How do I iterate over the words of a string?

  • 2008-10-25 08:58:21
  • Ashwin Nanjappa
  • 2138987 View
  • 2886 Score
  • 76 Answer
  • Tags:   c++ string split

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

57 Answered Questions

[SOLVED] How do I read / convert an InputStream into a String in Java?

19 Answered Questions

[SOLVED] Count the number occurrences of a character in a string

  • 2009-07-20 20:00:36
  • Mat
  • 911917 View
  • 908 Score
  • 19 Answer
  • Tags:   python string count

11 Answered Questions

[SOLVED] How do I get a substring of a string in Python?

  • 2009-03-19 17:29:41
  • Joan Venge
  • 2707290 View
  • 1982 Score
  • 11 Answer
  • Tags:   python string

31 Answered Questions

[SOLVED] endsWith in JavaScript

Sponsored Content