It's not necessary to set the return string @ret
all the time. You can first look for occurrences of @TargetStr@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 nth occurrence of x".