Tuesday, September 13, 2011

How many times a character (string) is repeated within a string

I am assuming in interviews this question "How to find the number of times a character or string is repeated within a string" seems to be asked quite often. May be that's why it is one of the popular question in forums :)

Normally people respond with a .NET based solution so i thought I would log a SQL Server based solution for this questions.


--Variable Declaration
DECLARE @strColumn VARCHAR(20)
DECLARE @searchString  VARCHAR(10)
DECLARE @intLen INT

--Variable Initialization
SET @strColumn = '123asd123asd123' 
SET @searchString ='123'
SET @intLen = LEN(@searchString)

--Solution
SELECT 
(
LEN(@strColumn) - 
LEN(REPLACE(@strColumn, @searchString, ''))
)/@intLen

No comments: