Skip to main content
added 18 characters in body
Source Link
Gert Arnold
  • 2k
  • 1
  • 17
  • 26

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

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 nth occurrence of x".

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 nth occurrence of x".

Source Link
Gert Arnold
  • 2k
  • 1
  • 17
  • 26

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 nth occurrence of x".