需求: 将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 |
+--------------------------------------------------------------------------------------------+