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
Yanick do you mean SELECT LEN(REPLACE(@test,’string’,’string+1char’) )– LEN(@test)?
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
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)
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 + ‘%’
Simple and clever. Thank you so much!
It is very much helpful to me and i used this site as i found perfect solution from here .
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
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
how to find how many commas present in this string in sql? ‘1,234,,erer,-28,,’
Very Smart Logic. Thanks.
Can i find just one char, says ‘?’ and return which col and row in each table
Please also provide code.
Many thanks
Thanks pinal it helped me a lot…
Thanks for the comment deepti.
How can we do this If there is multiple word to find from a big sentence without using any loop…
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   and · with ‘space’
Please approve request to Pinal/TSql for 5-20-15,·for Oracle conversion help
Marie
sorry the post example should have read with the replace values mixed in the note text.
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.
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
Good job !
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
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.