--> 数据库版本: --> 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]
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))) > 0drop table tb/* col ---------------------------------------------------------------------------------------------------- a/b/c/index.aspx(所影响的行数为 3 行) */
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)) endselect * from tb /* col ------------------------- a/b/c/index.aspx(所影响的行数为 3 行) */drop table tb
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;
牛逼,但是要是.net不是.com呢?
SELECT * ,STUFF([yuiuyi],1,CHARINDEX('.net',[yuiuyi])+4,'') FROM [TB]
SELECT REPLACE(REPLACE(SUBSTRING(REPLACE(REPLACE(web+'/','http://',''),'//','/'),CHARINDEX('.com',REPLACE(REPLACE(web+'/','http://',''),'//','/'))+5,1000)+'/','//','/')+'/','//','') FROM tb
--> 数据库版本:
--> 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]
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))) > 0drop table tb/*
col
----------------------------------------------------------------------------------------------------
a/b/c/index.aspx(所影响的行数为 3 行)
*/
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))
endselect * from tb
/*
col
-------------------------
a/b/c/index.aspx(所影响的行数为 3 行)
*/drop table tb
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;
,STUFF([yuiuyi],1,CHARINDEX('.net',[yuiuyi])+4,'')
FROM [TB]
REPLACE(REPLACE(SUBSTRING(REPLACE(REPLACE(web+'/','http://',''),'//','/'),CHARINDEX('.com',REPLACE(REPLACE(web+'/','http://',''),'//','/'))+5,1000)+'/','//','/')+'/','//','')
FROM tb