Mar 31, 2012

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
nthoccurence

    Choose :
  • OR
  • To comment
2 comments:
Write Comments
  1. Thanks for the above code. It really helps. But I found one thing, which is, it keeps on looping if the @occurrence_no is greater than what we have in the string. For example, if I have the same string as you mentioned in above example, but I have occurance_no as 4 or 5, then it would loop through again and get me 1st of 2nd value again. Is there anyway to return -1 or so when it does not find the occurance?

    Thanks again.

    ReplyDelete
  2. Dear Shireesha

    Thanks for the information. Good point noticed. I have updated the function to handle the same.

    ReplyDelete