SQL SERVER – Finding the Occurrence of Character in String

This article is written in response to provide hint to TSQL Beginners Challenge 14. The challenge is about counting the number of occurrences of characters in the string. Here is quick method how you can count occurrence of character in any string.

Here is quick example which provides you two different details.

  • How many times the character/word exists in string?
  • How many total characters exists in Occurrence?

Let us see following example and it will clearly explain it to you.

DECLARE @LongSentence VARCHAR(MAX)
DECLARE @FindSubString VARCHAR(MAX)
SET @LongSentence = 'My Super Long String With Long Words'
SET @FindSubString = 'long'
SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, ''))) CntReplacedChars,
(
LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars

This will return following resultset.

CntReplacedChars     CntOccuranceChars
-------------------- --------------------
2                    2

If there is single word to find in the character, both the answer will be same. Here is the example of the same.

DECLARE @LongSentence VARCHAR(MAX)
DECLARE @FindSubString VARCHAR(MAX)
SET @LongSentence = 'My Super Long String With Long Words'
SET @FindSubString = 'o'
SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, ''))) CntReplacedChars,
(
LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars

This will return following resultset.

CntReplacedChars     CntOccuranceChars
-------------------- --------------------
3                    3

I hope using this hint along with recursive CTE you will be able to solve the T-SQL Challenge. Additionally, make sure that your strings does not have leading or trailing empty spaces. If you have it, you may want to use LTRIM or RTRIM functions.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Bookmark Link for Sync Framework for SQL Azure
Next Post
SQLAuthority News – Speaking Online at Virtual Techdays – Aug 18, 2010 – Spatial Datatypes

Related Posts

43 Comments. Leave new

  • Yanick do you mean SELECT LEN(REPLACE(@test,’string’,’string+1char’) )– LEN(@test)?

    Reply
  • Here is i/p of a table:
    Name Surname unique id
    kunal & Raj Anand 1
    Ram & k kelly & R Singh 2
    Rahan & raj & kali kumar 3

    How to get o/p like this:–

    Name Surname unique id
    kunal anand 1
    Raj Anand 1
    Ram Singh 2
    k kelly Singh 2
    R Singh 2
    Rahan kumar 3
    raj kumar 3
    kali kumar 3

    Reply
  • I found method where programming isn’t necessary by using the new LIKE clause variations…you can just embed it within you query and no fancy CLR callouts to regex expressions are necessary…

    Here is an example of replacing any character values in a string with null…(CASE WHEN money_field LIKE ‘%[a-zA-Z]%’ THEN NULL ELSE money_field END)

    Reply
  • Here’s what I did when searching in job steps for text (see Occurrences column)
    ———————————
    ———————————
    ———————————
    DECLARE @SearchText VARCHAR(100)
    SET @SearchText = ‘DBCC INPUT’
    ———————————
    ———————————
    ———————————

    SELECT
    job.job_id,
    job.[name],
    CASE job.enabled
    WHEN 1 THEN ‘Yes’
    ELSE ‘No’
    END AS [Is Enabled],
    jobstep.[step_name],
    (LEN(jobstep.command) – LEN(REPLACE(jobstep.command COLLATE Latin1_General_CI_AS, @SearchText, ‘|’))) / LEN(@SearchText) + 1 AS [Occurrences],
    ‘…’ + SUBSTRING(jobstep.command, PATINDEX(‘%’ + @SearchText + ‘%’, jobstep.command COLLATE Latin1_General_CI_AS) – 20, 50) + ‘…’ AS [Found here]
    FROM
    msdb.dbo.sysjobs job
    inner join msdb.dbo.sysjobsteps jobstep on job.job_id = jobstep.job_id
    WHERE
    jobstep.command COLLATE Latin1_General_CI_AS LIKE ‘%’ + @SearchText + ‘%’

    Reply
  • Simple and clever. Thank you so much!

    Reply
  • kalindi patel
    March 7, 2013 5:23 pm

    It is very much helpful to me and i used this site as i found perfect solution from here .

    Reply
  • The simplest method of counting the no. of occurrences of any given character in a string ( in this example ‘N’ )

    Select LEN(ColumnToCheck) – LEN(REPLACE(ColumnToCheck,”N”,””)) From TheTable

    Reply
  • Hi Pinal,

    Need some help, can you please tell me how can one find the nth occurance of a string.

    Ex – “This is a long string in long sentence…” and I want to find to 5th occurance of the word.

    Something like “instrin” function in oracle which can give any occurance of a string in a sentence

    Reply
  • how to find how many commas present in this string in sql? ‘1,234,,erer,-28,,’

    Reply
  • Very Smart Logic. Thanks.

    Reply
  • Can i find just one char, says ‘?’ and return which col and row in each table
    Please also provide code.
    Many thanks

    Reply
  • Thanks pinal it helped me a lot…

    Reply
  • How can we do this If there is multiple word to find from a big sentence without using any loop…

    Reply
  • Hello Pinal,
    I am new to sql server and I am trying to convert Oracle regexp_replace code that finds MANY DIFFERENT sets of NVARCHAR strings within ONE single Column and replace with a space. Is there a simple translation of the below Oracle code for TSQL version?

    to_char(ltrim(
    regexp_replace(regexp_replace(
    dbms_lob.substr(n.txt,4000,1),’]+>| |·|’|“|̵|–|| |[ ]+’, ‘ ‘)
    ,'([[:cntrl:]])|(^\t)’,”)
    )) as NoteText,

    Example text – replace &#160 and &#183 with ‘space’
    Please approve request to Pinal/TSql for 5-20-15,·for Oracle conversion help

    Marie

    Reply
  • sorry the post example should have read with the replace values mixed in the note text.

    Reply
  • I need to do this sorry my english is not to good. Not contain more than 2 consecutive characters that is part or your username or your full name.

    Reply
  • Sorry my english is not to good I need to do this this is AD password rules, Not contain more than 2 consecutive characters that is part or your username or your full name.Can you maybe help

    Reply
  • Good job !

    Reply
  • This is not working for below string

    DECLARE @LongSentence VARCHAR(MAX)
    DECLARE @FindSubString VARCHAR(MAX)
    SET @LongSentence = ‘!(&&&) &&& &&&&’
    SET @FindSubString = ‘&’
    SELECT (LEN(@LongSentence) – LEN(REPLACE(@LongSentence, @FindSubString, ”))) CntReplacedChars,
    (LEN(@LongSentence) – LEN(REPLACE(@LongSentence, @FindSubString, ”)))/LEN(@FindSubString) CntOccuranceChars

    Reply
  • Robert Bondy
    April 3, 2018 7:11 pm

    Occcurence_Count = LEN(REPLACE(string_to_search,string_to_find,’~’))- LEN(REPLACE(string_to_search,string_to_find,”))

    This solution is a bit cleaner than many that I have seen, especially with no divisor.
    You can turn this into a function or use within a Select.
    No variables required.
    I use tilde as a replacement character, but any character that is not in the dataset will work.

    Reply

Leave a Reply

Menu