TSQL Script to find the number of occurrence of characters in a string

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 440
DECLARE @String VARCHAR(20) = 'Hello how r u?'

;WITH numberCTE AS
(
SELECT 1 AS Rn
UNION ALL
SELECT Rn+1 FROM numberCTE WHERE Rn<LEN(@String)
)
SELECT Character = SUBSTRING(@String,rn,1),Occurance = COUNT(SUBSTRING(@String,rn,1))
FROM numberCTE
WHERE LEN(SUBSTRING(@String,rn,1))>0
GROUP BY SUBSTRING(@String,rn,1)


Result
----------
Character	Occurance
? 1
e 1
h 2
l 2
o 2
r 1
u 1
w 1

Comments or Responses

Login to post response