May 4, 2016

Getting Nth Position Char Index

I had to write a function which will return NthCharIndex when text, subtext and position to return its char index. Below function will help to get it.

CREATE FUNCTION [GetNthCharIndex]
(
@text    nvarchar(MAX),
@subText nvarchar(MAX),
@numCharIndex INT
)
RETURNS INT
AS
BEGIN
DECLARE @startPos INT
DECLARE @subTextLen INT
DECLARE @subTextIndex INT
DECLARE @lastSubTextIndex INT
DECLARE @count INT = 0

SET @subTextLen = LEN(@subText)
SET @startPos = 1
SET @lastSubTextIndex = 0

SET @subTextIndex = CHARINDEX(@subText, @text, @startPos)

WHILE @subTextIndex > 0
BEGIN
IF @count = @numCharIndex
BREAK;

SET @lastSubTextIndex = @subTextIndex
SET @startPos = @subTextIndex + @subTextLen
SET @subTextIndex = CHARINDEX(@subText, @text, @startPos)
SET @count = @count + 1
END

RETURN @lastSubTextIndex
END


Result:

SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 1)

Output: 1

SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 2)

Output: 9

SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 3)

Output: 11

No comments: