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

Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio