Today I got hit by a simple problem by my friend.. He wanted to find out the position of a character within in a string when it came for the 4th time within that string… For example if the string is “abc;a123;afgh;ayui” and he wants to know the position of 4th a in the string above.
I suggested him to write a UDF like below which will do the trick.
The while loop can also be replaced with a CTE, but I put down the WHILE loop here for historical reasons.
IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'charNumIndex' and xtype = 'FN') drop function dbo.charNumIndex GO CREATE function dbo.charNumIndex ( @string varchar(4000) ,@lookupChar varchar(5) ,@repeatedTime INT ) RETURNS INT BEGIN declare @charPosition INT DECLARE @loop INT DECLARE @rtPosition INT set @charPosition = -1 SET @loop = 1 WHILE (CHARINDEX(@lookupChar,@string,(@charPosition+1)) <> 0) AND (@loop <= @repeatedTime) BEGIN SET @charPosition = CHARINDEX(@lookupChar,@string,(@charPosition+1)) SET @loop = @loop + 1 END IF (@loop > 2) SET @rtPosition = @charPosition ELSE SET @rtPosition = 0 RETURN @rtPosition END