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.

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.

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 (http://blog.sqlauthority.com)

39 thoughts on “SQL SERVER – Finding the Occurrence of Character in String

  1. Thank you. I have understood the concept but i need a explanation of every functions which have you used for that example for my better understand.


  2. I am sorry for all of you who have to do string manipulation in SQL during their day jobs because they are stuck in a legacy codebase.


  3. Cool example.
    Hi Fairoze,
    LEN – Returns the lenght of the string.
    REPLACE – allows one to replace characters in a given string.
    Check out books online for more examples and detailed explanation.


  4. Thanks again, Pinal.
    I really love these T-SQL-examples. Especially string-manipulation is hard in T-SQL compared to other languages, so thumbs up for sharing your knowledge.


  5. Hi Pinal,

    I am a regular reader of your blog, kindly accept my heartfelt thanks for sharing your wisdom. Thanks again for the great post and the t-sql examples. It seems there is a typo error in the output for the first example, it should be:

    CntReplacedChars CntOccuranceChars
    ——————– ——————–
    8 2

    Just to avoid any doubt for the readers.

    Thanks & Regards,


  6. Sir,
    I am studing your blog. It is very good for everyone who wants to learn SQL.
    Here in your blog based on Finding the occurrence of character in a string you gave two example. One is for caracter and other for word. Here i want to tell you that in the first example there is some problem. When i run the example i found that the result is different while in your case the result are same.

    CntReplaceChars CntOccurrenceChars.
    8 2


  7. ALTER FUNCTION [dbo].[CountWord]
    — Add the parameters for the function here
    @RowStr nvarchar(1000),
    @WordStr nvarchar(255)
    RETURNS int

    declare @WordCount int
    declare @index int

    set @index =0
    set @WordCount = 0

    while CHARINDEX(@WordStr, @RowStr,@index)>0

    SET @index = CHARINDEX(@WordStr,@RowStr,@index)
    SET @WordCount = @WordCount + 1
    if @index =0
    SET @index = @index + 1

    RETURN @WordCount



  8. Hi Pinal,

    Great stuff, but in regards to this comment of yours –

    “… 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.”

    Is there a reason for not using DATALENGTH instead of LEN?

    With the use of DATALENGTH you won’t have to LTRIM and RTRIM. Plus you can also search for space(s) as well.

    I have only carried out limit testing and have not done any searching for issue(s) with DATALENGTH.

    @varString VARCHAR(4000),
    @varCharPattern1 VARCHAR(20),
    @varCharPattern2 VARCHAR(20),
    @varCharPattern3 VARCHAR(20)

    SET @varString = ‘ Some random text aslkja aslkwe ld axls ‘
    SET @varCharPattern1 = ‘ ‘
    SET @varCharPattern2 = ‘as’
    SET @varCharPattern3 = ‘ ‘

    SELECT (DATALENGTH(@varString) – DATALENGTH(REPLACE(@varString, @varCharPattern1, ”)))/DATALENGTH(@varCharPattern1)
    SELECT (DATALENGTH(@varString) – DATALENGTH(REPLACE(@varString, @varCharPattern2, ”)))/DATALENGTH(@varCharPattern2)
    SELECT (DATALENGTH(@varString) – DATALENGTH(REPLACE(@varString, @varCharPattern3, ”)))/DATALENGTH(@varCharPattern3)




  9. above table

    Cityname operatorname prefix
    Kolkata Unitech Wireless 90620-90629 82960-82969
    Kolkata Datacom Solutions 90730-90739
    Kolkata BSNL 94330-94339 94320-94325 94770-94779
    Kolkata Loop 91100-91109
    Kolkata Reliance Telecom 98830-98839 96810-96819 88200-88209
    Mumbai BPL Mobile 98210-98219 96640-96649 97730-97739
    Mumbai Bharti Airtel 98670-98679 98920-98929 99670-99674

    Wanted output like mentione in below table

    Cityname operatorname prefix
    Kolkata Unitech Wireless 90620
    Kolkata Unitech Wireless 90621
    Kolkata Unitech Wireless 90622
    Kolkata Unitech Wireless 90623
    Kolkata Unitech Wireless 90624
    Kolkata Unitech Wireless 90625
    Kolkata Unitech Wireless 90626
    Kolkata Unitech Wireless 90627
    Kolkata Unitech Wireless 90628
    Kolkata Unitech Wireless 90629
    Kolkata Unitech Wireless 82960
    Kolkata Unitech Wireless 82969
    Kolkata Datacom Solutions 90730
    Kolkata Datacom Solutions 90731
    So on…..

    pls suggest


  10. Thought I would share this handy (but simple) function for finding the Nth occurrence of a character in a string.

    REF: http://www.sqlservercentral.com/scripts/Miscellaneous/30497/

    Also, an example of how I used it…

    –String value
    –tblData.UserLogString = ‘John.Doe,4,Johnathan,Doe,Johnathan.Doe@domain.com,True,False|’

    –Extract the UserID from string value.
    SUBSTRING( tblData.UserLogString, 0, CHARINDEX( ‘,’, tblData.UserLogString, 0 ) )
    Value returned: “John.Doe”

    –Extract the User Name (First Last) from string value.
    SUBSTRING( tblData.UserLogString, CHARINDEX2( ‘,’, tblData.UserLogString, 2 ) + 1, ( CHARINDEX2( ‘,’, tblData.UserLogString, 4 ) – CHARINDEX2( ‘,’, tblData.UserLogString, 2 ) ) – 1 ),
    ‘,’ ,
    ‘ ‘ )
    Value returned: “Johnathan Doe”

    –Extract the email ID from string value.
    CHARINDEX2( ‘,’, tblData.UserLogString, 4 ) + 1 ,
    ( CHARINDEX2( ‘,’, tblData.UserLogString, 5 ) –
    CHARINDEX2( ‘,’, tblData.UserLogString, 4 ) ) – 1 ),
    Value returned: “Johnathan.Doe@domain.com”

    I’m sure this can be done many other ways… Also I disagreed with the author in how I created the SPROC – I created it with the command:
    CREATE FUNCTION [dbo].[CharIndex2]


    (p.s. Pinal – I enjoy your column!)


  11. i want to create a slang filter function.

    want a query which returns no of slang in a string (select query).

    Table : Slans

    result required.
    string slang_cnt slangs
    aa cc bsf kk ff ee 3 aa, cc, ee


  12. A simple and efficient way to count the occurrences in a string:
    Here we are counting the number of As
    DECLARE @test VARCHAR(150)
    SET @test = ‘aaaaabbbbb’

    SELECT REPLACE(@test,’a’,’aa’) – LEN(@test)


  13. 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


  14. 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)


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

    CASE job.enabled
    WHEN 1 THEN ‘Yes’
    ELSE ‘No’
    END AS [Is Enabled],
    (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]
    msdb.dbo.sysjobs job
    inner join msdb.dbo.sysjobsteps jobstep on job.job_id = jobstep.job_id
    jobstep.command COLLATE Latin1_General_CI_AS LIKE ‘%’ + @SearchText + ‘%’


  16. 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


  17. 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


  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

  19. 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?

    dbms_lob.substr(n.txt,4000,1),’]+>| |·|’|“|̵|–|| |[ ]+’, ‘ ‘)
    )) as NoteText,

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



  20. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s