Finding the Nth occurrence of character in string

Question

I wrote an SQL Server function which returns the substring before the Nth occurrence of a character.

For example:

SELECT dbo.fn_getFirstNthSentence('.', 'hello world.It.is.raining.today', 3)

returns 'hello world.It.Is.' as a result.

The function I wrote looks dirty and slow so I want to optimize it. Any advice to make it clean is appreciated.

CREATE FUNCTION fn_getFirstNthSentence
(
  @TargetStr VARCHAR(MAX) ,
  @SearchedStr VARCHAR(8000) ,
  @Occurrence INT
)
RETURNS varchar(MAX)
AS
BEGIN

    DECLARE @pos INT ,
        @counter INT ,
        @ret INT;

    SET @pos = CHARINDEX(@TargetStr, @SearchedStr);

    IF ( @pos = 0 )
        RETURN @SearchedStr

    SET @counter = 1;

    IF @Occurrence = 1
        SET @ret = @pos;

    ELSE
        BEGIN

            WHILE ( @counter < @Occurrence )
                BEGIN

                    IF(LEN(@SearchedStr) < @pos + 1)
                        RETURN @SearchedStr

                    SELECT  @ret = CHARINDEX(@TargetStr, @SearchedStr,
                                             @pos + 1);
                    IF(@ret = 0)
                        RETURN @SearchedStr
                    SET @counter = @counter + 1;
                    SET @pos = @ret;
                END;
        END;
    RETURN LEFT(@SearchedStr, @ret)
END;

Answer 1

It's not necessary to set the return string @ret all the time. You can first look for occurrences of @TargetStr with an incrementing start_location parameter of the CHARINDEX function. Only after no (new) occurrences are found, or when the required max number of occurrences are found is it time to set the return string:

CREATE FUNCTION fn_getFirstNthSentence
(
  @TargetStr VARCHAR(MAX) ,
  @SearchedStr VARCHAR(8000) ,
  @Occurrence INT
)
RETURNS varchar(MAX)
AS
BEGIN
    DECLARE @counter INT = 0
          , @index INT = 0
          , @newIndex INT
          , @end BIT = 0;

    WHILE(@end = 0 AND @counter < @occurrence)
    BEGIN
        SET @counter = @counter + 1;
        SET @newIndex = CHARINDEX(@TargetStr, @SearchedStr, @index + 1);
        IF (@newIndex > 0)
            SET @index = @newIndex;
        ELSE
            SET @end = 1;
    END
    IF @index > 0 SET @index = @index - 1;
    RETURN LEFT(@SearchedStr, @index)
END

Note that this returns an empty string if no occurrences are found. I think that's more correct for a function that returns "everything until the n^th occurrence of x".