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` of the `CHARINDEX` function. Only after no 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<sup>th</sup> occurrence of *x*".