December 9, 2014 at 9:15 am
Comments posted to this topic are about the item split string between 2 patterns
December 30, 2014 at 3:19 am
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.
January 8, 2015 at 3:27 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply