比如说这样
declare @s nvarchar(4000)
set @s='http://www.google.com/dasd/dsadas'
select left(stuff(@s,1,7,''),charindex('/',stuff(@s,1,7,''),1)-1)
declare @s nvarchar(4000)
set @s='http://www.google.com/dasd/dsadas'
select left(stuff(@s,1,7,''),charindex('/',stuff(@s,1,7,''),1)-1)
set @s='http://www.baidu.com/fdsafds/dfsfds'
select substring(@s,charindex('/',@s,2)+2,charindex('/',@s,charindex('/',@s,2)+2)-charindex('/',@s,2)-2)
(a varchar(1000))insert into #tt
select 'http://www.google.com/dasd/dsadas'select * from #tt
select substring(substring(a,charindex('w',a),len(a)),1,charindex('/',substring(a,charindex('w',a),len(a)))-1) from #tt
set @s='http://www.baidu.com/fdsafds/dfsfds'
select substring(@s,charindex('/',@s,2)+2,charindex('/',@s,charindex('/',@s,2)+2)-charindex('/',@s,2)-2)
select substring(vcome,charindex('/',vcome,2)+2,charindex('/',vcome,charindex('/',vcome,2)+2)-charindex('/',vcome,2)-2) from [views]
其中views是表名,vcome是字段,出现向 substring 函数传递了无效的 length 参数的错误提示,各位高手帮我看看是为什么?应该则么改??
set @s='http://www.google.com/dasd/dsadas'
----------------實現語句--------------------------
select left(right( @s, len(@s)-charindex('//',@s)-1),charindex('/',right( @s, len(@s)-charindex('//',@s)-1)) -1 )
---------------結果------------------
www.google.com
set @s='http://www.google.com'
----------------實現語句--------------------------
select case when charindex('//',@s)>0 and charindex('/',right( @s, len(@s)-charindex('//',@s)-1)) <=0 then
right( @s, len(@s)-charindex('//',@s)-1)
when charindex('//',@s)>0 and charindex('/',right( @s, len(@s)-charindex('//',@s)-1)) >0 then
left(right( @s, len(@s)-charindex('//',@s)-1),charindex('/',right( @s, len(@s)-charindex('//',@s)-1)) -1 )
else
@s end
select case when charindex('//',vcome)>0 and charindex('/',right( vcome, len(vcome)-charindex('//',vcome)-1)) <=0 then
right( vcome, len(vcome)-charindex('//',vcome)-1)
when charindex('//',vcome)>0 and charindex('/',right( vcome, len(vcome)-charindex('//',vcome)-1)) >0 then
left(right( vcome, len(vcome)-charindex('//',vcome)-1),charindex('/',right( vcome, len(vcome)-charindex('//',vcome)-1)) -1 )
else
vcome end from [views]
insert into views
select 'http://www.google.com/dasd/dsadas' union all
select 'http://www.google.com' union all
select 'www.google.com'
-------------表views的值--------------
select * from views
vcome
--------
http://www.google.com/dasd/dsadas
http://www.google.com
www.google.com
-----------實現語句 為下面的----------
select case when charindex('//',vcome)>0 and charindex('/',right( vcome, len(vcome)-charindex('//',vcome)-1)) <=0 then
right( vcome, len(vcome)-charindex('//',vcome)-1)
when charindex('//',vcome)>0 and charindex('/',right( vcome, len(vcome)-charindex('//',vcome)-1)) >0 then
left(right( vcome, len(vcome)-charindex('//',vcome)-1),charindex('/',right( vcome, len(vcome)-charindex('//',vcome)-1)) -1 )
else
vcome end from [views]
------------------結果為--------------
www.google.com
www.google.com
www.google.com
declare @s varchar(1000)
set @s='http://www.google.com
----------------實現語句--------------------------
select left(right( @s, len(@s)-charindex('//',@s)-1),charindex('/',right( @s, len(@s)-charindex('//',@s)-1)) -1 /* 這個地方多減了一個1 */) charindex 當查找沒有服合條件時 值為0 ,如果再-1 那麼 left(字符串,-1) 就出錯了
好比如
select left('dd',-1) ----這樣當然會出錯了