27,579
社区成员
发帖
与我相关
我的任务
分享
;WITH testdata(url)AS(
SELECT 'http://m.163.com/szxzl/imagemvc/frmphotoview.aspx?&id=p-rl-cc7960ae87aa428c96e54049c3028407&d=2017-05-09&w=190&h=145' UNION
SELECT 'http://m.361.com/abcd/imagemvc/frmphsssotoview.aspx?&id=p-rl-cc7960ae87aa428c96e5ttt4049c3028407&d=2017-05-10&w=1900&h=1045'
)
SELECT t.url,p.*
,LEFT(p.rt, LEN(p.rt)- CHARINDEX('/',REVERSE(p.rt)))+'/'+CONVERT(VARCHAR,p.d,112)+'/'+p.id+'_'+p.w+'x'+p.h+'.jpg' as addr
FROM testdata AS t
CROSS APPLY(VALUES(CONVERT(XML,'<n r="'+REPLACE(REPLACE(t.url,'=','="'),'&','"></n><n ')+'"></n>'))) c(x)
CROSS APPLY(
SELECT tt.n.value('n[1]/@r','varchar(100)') AS rt
,tt.n.value('n[2]/@id','varchar(100)') AS id
,tt.n.value('n[3]/@d','DATE') AS d
,tt.n.value('n[4]/@w','varchar(100)') AS w
,tt.n.value('n[5]/@h','varchar(100)') AS h
FROM c.x.nodes('/') tt(n)
) p
+-----------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+------------------------------------------+------------+------+------+------------------------------------------------------------------------------------------------+
| url | rt | id | d | w | h | addr |
+-----------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+------------------------------------------+------------+------+------+------------------------------------------------------------------------------------------------+
| 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/frmphotoview.aspx? | p-rl-cc7960ae87aa428c96e54049c3028407 | 2017-05-09 | 190 | 145 | http://m.163.com/szxzl/imagemvc/20170509/p-rl-cc7960ae87aa428c96e54049c3028407_190x145.jpg |
| http://m.361.com/abcd/imagemvc/frmphsssotoview.aspx?&id=p-rl-cc7960ae87aa428c96e5ttt4049c3028407&d=2017-05-10&w=1900&h=1045 | http://m.361.com/abcd/imagemvc/frmphsssotoview.aspx? | p-rl-cc7960ae87aa428c96e5ttt4049c3028407 | 2017-05-10 | 1900 | 1045 | http://m.361.com/abcd/imagemvc/20170510/p-rl-cc7960ae87aa428c96e5ttt4049c3028407_1900x1045.jpg |
+-----------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+------------------------------------------+------------+------+------+------------------------------------------------------------------------------------------------+
DECLARE @str NVARCHAR(MAX)= 'http://m.163.com/szxzl/imagemvc/frmphotoview.aspx?&id=p-rl-cc7960ae87aa428c96e54049c3028407&d=2017-05-09&w=190&h=145'
SELECT SUBSTRING(@str, 1, CHARINDEX('/frmphotoview.aspx?', @str))
+ CONVERT(NVARCHAR(100), CONVERT(DATE, SUBSTRING(@str,
CHARINDEX('&d', @str)
+ 3,
CHARINDEX('&w', @str)
- CHARINDEX('&d',
@str) - 3)), 112)
+ '/' + SUBSTRING(@str, CHARINDEX('id=', @str) + 3,
CHARINDEX('&d', @str) - CHARINDEX('id=', @str) - 3)
+ '_' + SUBSTRING(@str, CHARINDEX('w=', @str) + 2,
CHARINDEX('&h', @str) - CHARINDEX('w=', @str) - 2)
+ '*' + SUBSTRING(@str, CHARINDEX('h=', @str) + 2,
LEN(@str) - CHARINDEX('h=', @str)) + '.jpg'
DECLARE @Str VARCHAR(max)='http://m.163.com/szxzl/imagemvc/frmphotoview.aspx?&id=p-rl-cc7960ae87aa428c96e54049c3028407&d=2017-05-09&w=190&h=145'
SELECT LEFT(@Str,CHARINDEX('/frmphotoview.aspx?',@str))+REPLACE(SUBSTRING(@Str,CHARINDEX('&d=',@Str)+3,10),'-','')+'/'
+SUBSTRING(@Str,CHARINDEX('&id=',@Str)+4,CHARINDEX('&d=',@Str)-CHARINDEX('&id=',@Str)-4)+'_'
+SUBSTRING(@Str,CHARINDEX('&w=',@Str)+3,CHARINDEX('&h=',@Str)-CHARINDEX('&w=',@Str)-3)+'*'
+RIGHT(@Str,CHARINDEX('=h&',REVERSE(@Str))-1)+'.jpg'
/*
(无列名)
http://m.163.com/szxzl/imagemvc/20170509/p-rl-cc7960ae87aa428c96e54049c3028407_190*145.jpg
*/