How to get a id from a url using SQL QUERY. The ID changes dynamically. the database is Sql server 2008 -

i have url like:

http://mysites.xyz.com/_layouts/ng/activitystream.aspx/id/2624d92223261d370d7287c9e83caeea/activity%20stream%20post. 

i need 2624d92223261d370d7287c9e83caeea. not able so. current query

 substring(@url2,len('http://mysites.xyz.com/_layouts/ng/activitystream.aspx/id/'),len(@url2)-len('http://mysites.xyz.com/_layouts/ng/activitystream.aspx/id/')- charindex('/',reverse(@url2)))) 

please suggest.

try this:

select substring(        @url2,         charindex('/id/', @url2)+4,         charindex('/', @url2, charindex('/id/', @url2)+5)         - (charindex('/id/', @url2)+4)) 

note: assumes id followed @ least 1 more slash.

breakdown:
first argument of substring string contains full expression.
second 1 first index after /id/. third 1 desired length - calculated first index of / after /id/ - first index after /id/.

update

to cope strings not contain slash after id value, use case:

select substring(        @url,         charindex('/id/', @url)+4,         case when charindex('/', @url, charindex('/id/', @url)+5) > 0        charindex('/', @url, charindex('/id/', @url)+5)         - (charindex('/id/', @url)+4)        else           len(@url)        end        )