sql server如何解析url 参数

http://img.163.com.cn/szxzl/imagemvc/fiew.aspx?&id=e-rl-e30cd4c91f354f24bbf5c6db1c2af0c9&d=2017-06-08&w=190&h=145

需求: 将url中id、d两个参数解析出来,然后拼接成http://img.163.com.cn/szxzl/20170608/e-rl-e30cd4c91f354f24bbf5c9.jpg
要求:封装成sql 函数

sql server 2005+直接用正则表达式就可以了。

 Alter function fn_ParseURL
(
  @url nvarchar(max)
 )
 returns nvarchar(max)
 begin
    declare @s nvarchar(max)
    set @url='<n r="'+REPLACE(REPLACE(replace(@url,'?',''),'=','="'),'&','" ')+'"></n>'

    declare @xml xml=convert(XML, @url)

    select @s=LEFT(url.p.value('n[1]/@r','varchar(max)') , LEN(url.p.value('n[1]/@r','varchar(max)') )- CHARINDEX('/',REVERSE(url.p.value('n[1]/@r','varchar(max)') )))
              +'/'+CONVERT(VARCHAR,url.p.value('n[1]/@d','DATE'),112)+'/'+url.p.value('n[1]/@id','varchar(100)') +'_'+url.p.value('n[1]/@w','varchar(100)') +'x'+url.p.value('n[1]/@h','varchar(100)')+'.jpg'
         /*   url.p.value('n[1]/@r','varchar(max)') AS rt,url.p.value('n[2]/@id','varchar(100)') AS id
           ,url.p.value('n[1]/@d','DATE') AS d
           ,url.p.value('n[1]/@w','varchar(100)') AS w
           ,url.p.value('n[1]/@h','varchar(100)') AS h
           */
    from @xml.nodes('/') url(p)

    return @s
end
go
select dbo.fn_ParseURL(N'http://m.163.com/szxzl/imagemvc/frmphotoview.aspx?&id=p-rl-cc7960ae87aa428c96e54049c3028407&d=2017-05-09&w=190&h=145')

+--------------------------------------------------------------------------------------------+
| |
+--------------------------------------------------------------------------------------------+
| http://m.163.com/szxzl/imagemvc/20170509/p-rl-cc7960ae87aa428c96e54049c3028407_190x145.jpg |
+--------------------------------------------------------------------------------------------+