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)ASBEGIN    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;
Finding the Nth occurrence of character in string
strings;sql;sql server