请大虾帮忙处理一个更新,在线等.
数据库中有一个字段名为ff
内容如下：
//163.com
//hao123.com/
2122.232.232.05
13sd21.232.232.02:8080
//13sd21.232.232.02:8080
//13sd21.232.232.02:8080/
数据库中有大量的这样的数据
那以上为例，通过处理后的结果如下：
163.com
hao123.com
2122.232.232.05
13sd21.232.232.02
语句应该怎么写啊
[311 byte] By [
lovezgs] at [2008-1-9]
select left(replace(ff,'//',''),charindex(':')-1) from table
select case charindex(ff,':') when 0 then replace(ff,'//','') else left(replace(ff,'//',''),charindex(ff,':')-1) end from table
用update,注意把table1改成你的表名(ff所在表的名称):
update table1 set ff= replace(ff,'//','')
where charindex(ff,'//')>0;
update table1 set ff=left(ff,charindex(ff,':')-1)
where charindex(ff,':')>0;
那如果”//“前面还有字符呢？
或”/“后又字符
也学要去掉，怎么办啊
能帮忙写一下么？
我用的就是substring()
可是有问题
我写的语句是
update table set ff=substring(ff,charindex('//',ff,1)+2,charindex('/',ff,1)-charindex('//',ff,1)-2) where ff like '%//%'
update table set ff=substring(ff,charindex('//',ff,1)+2,charindex(':',ff,1)-charindex('//',ff,1)-2) where ff like '%//%'
declare @t table(col varchar(100))
insert @t select '//163.com'
union all select '//hao123.com/'
union all select '2122.232.232.05'
union all select '13sd21.232.232.02:8080'
union all select '//13sd21.232.232.02:8080'
union all select '//13sd21.232.232.02:8080/'
select case when charindex(':',col)>0 then left((replace(col,'/','')),charindex(':',(replace(col,'/','')))-1) else replace(col,'/','') end
from @t
