split string between 2 patterns

• ece.viky

  Right there with Babe

  Points: 727

  More actions

  December 9, 2014 at 9:15 am

  #295816

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

• toonjamie

  SSChasing Mays

  Points: 648

  More actions

  December 30, 2014 at 3:19 am

  #1766954

  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

  e.g.

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

  Jamie.

• ece.viky

  Right there with Babe

  Points: 727

  More actions

  January 8, 2015 at 3:27 am

  #1768721

  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)

  as

  begin

  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)))

  else

  set @returnstring = 'pattern not exists'

  return (@returnstring)

  end

  go