TSQL — Find position of a repeated character in the string


Howdy Everyone,

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

EXAMPLE:
select left(‘http://abcd/abc/abcd&#8217;,dbo.charNumIndex(‘http://abcd/abc/abcd&#8217;,’/’,4))

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s