Find occurrences of character in string SQL Server

We can use some tricky method to find total number of occurrences of a character
in a string in SQL Server.

DECLARE @Str NVARCHAR(100)
SET @Str = '0XXXRR11ds11xx1'
SELECT LEN(@Str) - LEN(REPLACE(@Str,'1','')) 'Total No Of Occurrences'

Now if i want to my search should be case sensitive then we need to use COLLATE in that case.

DECLARE @Str NVARCHAR(100)
SET @Str = '0XXXRR11ds11xx1'
SELECT LEN(@Str) - LEN(REPLACE(@Str COLLATE SQL_Latin1_General_Cp1_CS_AS,'x' 
COLLATE SQL_Latin1_General_Cp1_CS_AS,'')) 
'Total No Of Occurrences'

That’s It.
Enjoy Learning.