Find nth Occurrence of Character SQL Server

CHARINDEX function provided by Microsoft in SQL Server is used to find the index of a character or substring in the main string, but it only returns the first occurrence.

Oftentimes one needs to find the Nth Occurrence of a character or sub-string. In this post I am sharing a function to achieve the same.

The function accept 3 parameters and returns the position of Nth Occurrence of character or sub-string in the main string.

1. string: input value i.e main string
2. occurrence_val: character or sub-string to be find in the main string
3. occurrence_no: nth occurrence to be find in the string

CREATE FUNCTION dbo.udf_GetNthOccurrence(@string VARCHAR(MAX), @occurrence_val VARCHAR(MAX), @occurrence_no INT)
RETURNS INT AS
BEGIN
    DECLARE @ctr INT, @pos INT, @len INT
    SET @ctr = 0
    SET @pos = 0
    SET @len = DATALENGTH(@occurrence_val)
    WHILE @ctr<@occurrence_no
    BEGIN       
        SET @pos = CHARINDEX(@occurrence_val, @string, @pos) + @len
    IF @pos = @len
        BEGIN
            RETURN -1
        END
        SET @ctr = @ctr+1           
    END
    RETURN @pos - @len
END

Let's use above created function and try to find the nth occurrence in main string

DECLARE @String VARCHAR(MAX), @Occ_No INT, @Occ_String VARCHAR(5)
SET @String = 'Ajay\Vijay\Amit\Sanjay'

SET @Occ_No = 2
SET @Occ_String = '\'
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1) AS Left_Part
        , SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No), LEN(@String)) AS Right_Part
SET @String = 'Ajay\\Vijay\\Amit\\Sanjay'

SET @Occ_No = 3
SET @Occ_String = '\\'
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1) AS Left_Part
        , SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No), LEN(@String)) AS Right_Part

SET @Occ_No = 4
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , CASE  WHEN dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) = -1
                THEN 'Occurent Not found'
                ELSE LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1)
          END AS Left_Part
        , CASE  WHEN dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) = -1
                THEN 'Occurent Not found'
                ELSE SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)+1, LEN(@String))
          END AS Right_Part

OUTPUT