split string between 2 patterns

  • Comments posted to this topic are about the item split string between 2 patterns

  • Nice function.

    But, errors (Invalid length parameter passed to the LEFT or SUBSTRING function) if both search patterns exist and @secondpattern appears BEFORE @firstpattern.

    So I would enhance your function by checking for

    if @end > @start

    and if not swap the values over.

    You could be really flexible and add another parameter

    , @find_in_any_order bit = 1

    which enabled/disabled a successful valid return string


    select mydb.dbo.fn_split_btw_patterns( 'abcdefghij', 'hi', 'cd', 1)


  • hi,

    pls use this altered function , which will give you the desired result.

    alter function fn_split_btw_patterns(@inputstring varchar(max),@firstpattern varchar(max),@secondpattern varchar(max))

    returns varchar(max)



    declare @returnstring varchar(max)=''

    declare @start int

    declare @end int

    select @start = patindex('%' + @firstpattern + '%', @inputstring) ,

    @end = patindex('%' + @secondpattern + '%', @inputstring)

    if @start > 0 and @end > 0 and @end > @start

    set @returnstring = (substring(@inputstring, @start + len(@firstpattern),( @end - @start ) - len(@firstpattern)))


    set @returnstring = 'pattern not exists'

    return (@returnstring)



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

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