How do I find the length of a pattern?

Hi
I want to get the substring of a pattern match but I'm not sure how to
reliably get the length of the pattern as well as the start position. I'm
looking for html encoding strings, i.e. '%&%;%'. Please see below.
declare @.str varchar(100)
declare @.len int, @.start int
Set @.str = 'bob&burt'
set @.start = patindex('%&%;%',@.str)
set @.len = patindex('%;%',@.str)
print SUBSTRING ( @.str , @.start , @.len-@.start+1 )
-- works
Set @.str = ';bob&burt'
set @.start = patindex('%&%;%',@.str)
set @.len = patindex('%;%',@.str)
print SUBSTRING ( @.str , @.start , @.len-@.start+1 )
-- causes an error
Many thanks
AndrewI'm not sure if it will help you as-is, but here is a function I wrote that
solves a very similar problem: finding the end of a pattern. In order to
get the length of a match, I assume you need the end of the match...
http://www.sqljunkies.com/WebLog/am...plitString.aspx
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew Jocelyn" <andrew.jocelyn@.REMOVETHIS.empetus.co.uk> wrote in message
news:u8w129MoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> Hi
> I want to get the substring of a pattern match but I'm not sure how to
> reliably get the length of the pattern as well as the start position. I'm
> looking for html encoding strings, i.e. '%&%;%'. Please see below.
> declare @.str varchar(100)
> declare @.len int, @.start int
> Set @.str = 'bob&burt'
> set @.start = patindex('%&%;%',@.str)
> set @.len = patindex('%;%',@.str)
> print SUBSTRING ( @.str , @.start , @.len-@.start+1 )
> -- works
> Set @.str = ';bob&burt'
> set @.start = patindex('%&%;%',@.str)
> set @.len = patindex('%;%',@.str)
> print SUBSTRING ( @.str , @.start , @.len-@.start+1 )
> -- causes an error
> Many thanks
> Andrew
>