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

User · Answer

This will return number of occurance of N   select ColumnName  LEN ColumnName - LEN REPLACE ColumnName   N        from Table

User · Answer

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

User · Answer

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

User · Answer

In SQL Server   SELECT LEN REPLACE myColumn   N         FROM

User · Answer

Maybe something like this     SELECT     LEN REPLACE ColumnName   N        as NumberOfYs FROM     SomeTable

User · Answer

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  lt   length  main string    It determines the number of single character occurrences as well as the sub-string occurrences in main string

User · Answer

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

User · Answer

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

User · Answer

try this  declare  v varchar 250     test a 1   hheuw-20   -- LF     select len replace  v      11   -len  v

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The easiest way is by using Oracle function   SELECT REGEXP COUNT COLUMN NAME  CONDITION   FROM TABLE NAME

User · Answer

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

User · Answer

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

User · Answer

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  gt 0     BEGIN         WHILE  position  lt   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

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

Examples related to sql

Examples related to string

Examples related to tsql

Examples related to count

Examples related to symbols