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".