Technical Article

split string between 2 patterns

,

This function is used to split the string between two patterns.

Here with example :

declare @inputstring varchar(max)=

'/mobileapi/property/postad.aspx?mode=postanad&deposit=100000&appversion=5.1&areaunit=Sq+Feet&TransactionType=rent'

declare @firstpattern varchar(max)='&deposit='

declare @secondpattern varchar(max)='&appversion='

select dbo.fn_split_btw_patterns(@inputstring,@firstpattern,@secondpattern)

Regards,

Vignesh Arulmani

create 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
set @returnstring = (substring(@inputstring, @start + len(@firstpattern),( @end - @start ) - len(@firstpattern)))
else 
set @returnstring = 'pattern not exists'

return (@returnstring)
end
go

--examples
declare @inputstring varchar(max)=
'/mobileapi/property/postad.aspx?mode=postanad&deposit=100000&appversion=5.1&areaunit=Sq+Feet&TransactionType=rent'
declare @firstpattern varchar(max)='&deposit='
declare @secondpattern varchar(max)='&appversion='
select dbo.fn_split_btw_patterns(@inputstring,@firstpattern,@secondpattern)

Rate

1.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

1.67 (3)

You rated this post out of 5. Change rating