Post reply

Return VARCHAR Between 2 Character sets

  • November 14, 2016 at 11:09 am

    #331827

    Comments posted to this topic are about the item Return VARCHAR Between 2 Character sets

  • November 23, 2016 at 6:34 am

    #1914209

    Thanks for the script.

  • November 29, 2016 at 9:47 am

    #1914994

    here i have another aproach to that function, i think its more clear and readable aproach

    CREATE FUNCTION [fn_BLD_StrBetween_2] (

    @ObjectNameVARCHAR(MAX)

    ,@FindStartVARCHAR(200)

    ,@FindEndVARCHAR(200)

    ,@OffsetINT

    ,@StringNotFoundVARCHAR(MAX)

    )

    RETURNS VARCHAR(MAX)

    AS

    BEGIN

    DECLARE @OUTPUTVARCHAR(MAX)

    , @STARTINT

    , @ENDINT

    ;WITH MEASURES AS (

    SELECTISNULL(@Offset, 0)AS Offset

    , ISNULL(CHARINDEX(@FindStart, @ObjectName), 1)AS FindStart

    , ISNULL(DATALENGTH(@FindStart), 0)AS FindStart_len

    , ISNULL(CHARINDEX(@FindEnd, @ObjectName), DATALENGTH(@ObjectName) + 1)AS FindEnd

    , ISNULL(DATALENGTH(@FindEnd), 0)AS FindEnd_len

    , CHARINDEX(@FindStart, @ObjectName)AS _start

    , CHARINDEX(@FindEnd, @ObjectName)AS _end

    )

    SELECT@OUTPUT = SUBSTRING(@ObjectName

    , FindStart + FindStart_len + Offset

    , FindEnd - FindStart - FindStart_len )

    , @START= ISNULL(_start, 0)

    , @END= ISNULL(_end, 0)

    FROMMEASURES

    SET@OUTPUT = CASE

    WHEN @START + @END = 0

    THEN ISNULL(@StringNotFound, @ObjectName)

    ELSE @OUTPUT

    END

    RETURN @OUTPUT

    END;

    EDIT:

    replace the LEN() with DATALENGTH(), LEN() dont count the trailing spaces

    but just because we use VARCHAR, in case of NVARCHAR we must divide ir by two, cause DATALENGTH() counts the number of bytes and on a NVARCHAR each character counts as 2 bytes

  • November 29, 2016 at 10:29 am

    #1915003

    Thanks Paulo!

    Much more elegant and Readable Approach. Much appreciate the input!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply