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)
43 Comments. Leave new
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.
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.
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.
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.
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,
Animesh
Hi Pinal,
Thanks for the SQL. I love your articles. Very good. Continue this effort.
Take care,
Jagan.
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
ALTER FUNCTION [dbo].[CountWord]
(
— Add the parameters for the function here
@RowStr nvarchar(1000),
@WordStr nvarchar(255)
)
RETURNS int
AS
BEGIN
declare @WordCount int
declare @index int
set @index =0
set @WordCount = 0
while CHARINDEX(@WordStr, @RowStr,@index)>0
begin
SET @index = CHARINDEX(@WordStr,@RowStr,@index)
SET @WordCount = @WordCount + 1
if @index =0
break
else
SET @index = @index + 1
end
RETURN @WordCount
END
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.
DECLARE
@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)
Thanks,
Hien
good
thanks a lot
Regards
Ajit
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
Dear Pinal Sir,
Thanks a lot, your nots are always very helpful to me.
Hello Pinal,
It is very interesting example, and your example is marvelous..
salute to you
Very handy. Thank you!
can u plz tell how to fing a name which contains only 2 time “a”
thanks for u r cmnt….
Thought I would share this handy (but simple) function for finding the Nth occurrence of a character in a string.
REF:
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.
REPLACE(
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.
SUBSTRING(tblData.UserLogString,
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]
Cheers,
(p.s. Pinal – I enjoy your column!)
i want to create a slang filter function.
want a query which returns no of slang in a string (select query).
i.e.
Table : Slans
slangs
—————–
aa
bb
cc
dd
ee
result required.
===================
string slang_cnt slangs
====================================================
aa cc bsf kk ff ee 3 aa, cc, ee
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)
Intelligent way, Sir :)