SQL 函数解析url

阿飞7587 2017-07-28 01:54:38
http://m.163.com/szxzl/imagemvc/frmphotoview.aspx?&id=p-rl-cc7960ae87aa428c96e54049c3028407&d=2017-05-09&w=190&h=145


将上面url参数提取出来,生成

http://m.163.com/szxzl/imagemvc/20170509/p-rl-cc7960ae87aa428c96e54049c3028407_190x145.jpg
...全文
573 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
道素 2017-07-31
  • 打赏
  • 举报
回复

;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 |
+-----------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+------------------------------------------+------------+------+------+------------------------------------------------------------------------------------------------+
二月十六 2017-07-28
  • 打赏
  • 举报
回复
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'


中国风 2017-07-28
  • 打赏
  • 举报
回复
e.g.
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
*/
卖水果的net 2017-07-28
  • 打赏
  • 举报
回复
建议用应用程序来处理这些数据。

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧