How to count duplicate characters using sql server

Question

How to count duplicate characters using sql server for example place: chennai from that i want to count number of n's from chennai using sqlserver 2000 thanks Rgds narasiman P

Jignesh Trivedi · Answer

Hi In this case Scalar value function help you. Try.. CREATE FUNCTION [dbo].[fnValue](@String varchar(max) @CharToFind char(1)) RETURNS INT AS BEGIN DECLARE @idx int DECLARE @countChar int SET @countChar = 0 DECLARE @slice varchar(max) SELECT @idx = 1 IF LEN(@String) RETURN @countChar WHILE @idx!= 0 BEGIN SET @slice = LEFT(@String 1) IF(@slice=@CharToFind) set @countChar = @countChar + 1 SET @String = right(@Stringlen(@String) - @idx) IF LEN(@String) = 0 BREAK END RETURN @countChar END select dbo.fnValue('chennai''n') --Output --2 hope this help you.