SQL语句写法

Question

表 upload 的urls 字段有以下地址，请将地址转化为本地相对地址，每个地址占一行输出，例如：/xxxxxxxfile/2012-1/201211010285838684.gif
/xxxxxxxfile/2012-1/201211010324745788.gif
/xxxxxxxfile/2012-1/201211010343215757.gif原地址：http://pic.xxxxxxx.com/xxxxxxxfile/2012-1/201211010285838684.gif
http://pic.xxxxxxx.com/xxxxxxxfile/2012-1/201211010324745788.gif
http://i2.xxxxxxx.com/xxxxxxxfile/2012-1/201211010343215757.gif
http://i1.xxxxxxx.com/xxxxxxxfile/2012-1/201211010352419959.gif
http://i.xxxxxxx.com/xxxxxxxfile/2012-1/201211010413081870.gif
http://i1.xxxxxxx.com/xxxxxxxfile/2012-1/201211216511427261.jpg|http://i2.xxxxxxx.com/xxxxxxxfile/2012-1/201211216523865016.jpg|http://i.xxxxxxx.com/xxxxxxxfile/2012-1/201211216532952079.jpg|http://i2.xxxxxxx.com/xxxxxxxfile/2012-1/201211216541184648.jpg|http://i2.xxxxxxx.com/xxxxxxxfile/2012-1/20121121655085686.jpg|http://i.xxxxxxx.com/xxxxxxxfile/2012-1/201211216561244736.jpg|http://i.xxxxxxx.com/xxxxxxxfile/2012-1/20121121657813848.jpg|http://i.xxxxxxx.com/xxxxxxxfile/2012-1/20121121703424968.jpg|http://pic.xxxxxxx.com/xxxxxxxfile/2012-1/20121121658215029.jpg|http://pic.xxxxxxx.com/xxxxxxxfile/2012-1/20121121714767468.jpg

Answer 1

使用charindex函数，取/xxxxxxxfile开始的那个位置，然后获取字符串后面的部分。

Answer 2

--你的数据还有|分隔的，要先拆分然后替换，挺麻烦的
declare @x varchar(1000);
select @x='http://pic.xxxxxxx.com/xxxxxxxfile/2012-1/201211010285838684.gif'
select right(@x,len(@x)-patindex('%com/%',@x)-4)
--or
select right(@x,len(@x)-charindex('com/',@x)-4)
/*
xxxxxxfile/2012-1/201211010285838684.gif
*/