Oct 29, 2015

Find char index in string after Nth occurrence SQL server

This function returns position or index of characters in a string after give number of occurrence (nth) of same character:

CREATE FUNCTION CharIndexAfterN (
      @Char VARCHAR(10)
      ,@String VARCHAR(MAX)
      ,@Occurance INT
      )
RETURNS INT
AS
BEGIN
      DECLARE @Index AS INT = 1

      WHILE @Occurance <> 0
      BEGIN
            SET @Index = CHARINDEX(@Char, @String, @Index + 1)
            SET @Occurance -= 1
      END

      RETURN @Index
END

For example:
SELECT dbo.CharIndexAfterN('ab', '***ab****ab*******ab*', 2)


Output: 10

No comments:

Post a Comment