HeavenCore | IT Solutions Built on Open Source Software
View Thread
 Print Thread
Function to get occurrence of char (charindex + occurrence)
admin
Function to get occurrence of char (charindex + occurrence)


CREATE FUNCTION CHARINDEX2
    (
      @TargetStr VARCHAR(8000) ,
      @SearchedStr VARCHAR(8000) ,
      @Occurrence INT
    )
RETURNS INT
AS
    BEGIN
        DECLARE @pos INT ,
            @counter INT ,
            @ret INT

        SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
        SET @counter = 1

        IF @Occurrence = 1
            SET @ret = @pos
        ELSE
            BEGIN

                WHILE ( @counter < @Occurrence )
                    BEGIN
                        SELECT  @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)   
                        SET @counter = @counter + 1
                        SET @pos = @ret
                    END
            END
        RETURN(@ret)

    END

Jordon Pilling | Heavencore Administrator
 
Jump to Forum