求一条sql语句，替换所有网址

[D6Space]

数据表中一个字段url可能的形式如下
http://www.abc.com
http://www.bcd.com/
http://www.cab.com/a/b/c/index.aspx


求一条语句把网址全部替换成空，结果如下
''
''
a/b/c/index.aspx

[D6Space]

补充：www.abc.com 不是固定的，也可能是www.bcd.com或www.cab.com 都有可能，绝不是 replace('http://www.abc.com','')这么简单

[王向飞]

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 
AND type in (N'U')) 
DROP TABLE [TB]
GO

---->建表
create table [TB]([yuiuyi] varchar(35))
insert [TB]
select 'http://www.abc.com' union all
select 'http://www.bcd.com/' union all
select 'http://www.cab.com/a/b/c/index.aspx'
GO



--> 查询结果
SELECT * 
,STUFF([yuiuyi],1,CHARINDEX('.com',[yuiuyi])+4,'')
FROM [TB]
--> 删除表格
--DROP TABLE [TB]

[dawugui]

create table tb(col varchar(100))
insert into tb values('http://www.abc.com')
insert into tb values('http://www.abc.com/')
insert into tb values('http://www.abc.com/a/b/c/index.aspx')
go

--查询
select '' col from tb where charindex('/',substring(col,8,len(col))) = 0
union all
select substring(substring(col,8,len(col)),charindex('/',substring(col,8,len(col))) + 1,len(col)) col from tb where charindex('/',substring(col,8,len(col))) > 0

drop table tb

/*
col                                                                                                  
---------------------------------------------------------------------------------------------------- 


a/b/c/index.aspx

（所影响的行数为 3 行）
*/

[dawugui]

create table tb(col varchar(100))
insert into tb values('http://www.abc.com')
insert into tb values('http://www.abc.com/')
insert into tb values('http://www.abc.com/a/b/c/index.aspx')
go

--查询
select '' col from tb where charindex('/',substring(col,8,len(col))) = 0
union all
select substring(substring(col,8,len(col)),charindex('/',substring(col,8,len(col))) + 1,len(col)) col from tb where charindex('/',substring(col,8,len(col))) > 0
/*
col                      
-------------------------


a/b/c/index.aspx

（所影响的行数为 3 行）
*/

--更新
update tb set col = case when charindex('/',substring(col,8,len(col))) = 0 then ''
                    else substring(substring(col,8,len(col)),charindex('/',substring(col,8,len(col))) + 1,len(col))
                    end

select * from tb
/*
col                      
-------------------------


a/b/c/index.aspx

（所影响的行数为 3 行）
*/

drop table tb

[xman_78tom]

declare @t table(s varchar(256));
insert into @t 
 select 'http://www.abc.com' union all
 select 'http://www.abc.com/' union all
 select 'http://www.abc.com/a/b/c/index.aspx';
 
select STUFF(s,1,
 (case when CHARINDEX('/',s,8)>0 then CHARINDEX('/',s,8) else LEN(s) end),
'') from @t;

[D6Space]

[Quote=引用 2 楼 wxf163 的回复:]
SQL code

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE……
[/Quote]


牛逼，但是要是.net不是.com呢？

[王向飞]

SELECT * 
,STUFF([yuiuyi],1,CHARINDEX('.net',[yuiuyi])+4,'')
FROM [TB]

[D6Space]

都是高手，牛逼！小弟佩服！！

[billpu]

SELECT 
REPLACE(REPLACE(SUBSTRING(REPLACE(REPLACE(web+'/','http://',''),'//','/'),CHARINDEX('.com',REPLACE(REPLACE(web+'/','http://',''),'//','/'))+5,1000)+'/','//','/')+'/','//','')
FROM tb