DECLARE @str varchar(100) SET @str = 'C:\Documents and Settings\10015381.QINGDAOTRE\デスクトップ\評価結果100.xls'select reverse(substring(reverse(@str),0,charindex('\',reverse(@str))))/* --------------- 評価結果100.xls(1 行受影响) */
declare @s varchar(1000) set @s='"C:\Documents and Settings\10015381.QINGDAOTRE\デスクトップ\評価結果100.xls"' select substring(@s,len(@s)-charindex('\',REVERSE (@s))+2,len(@s))
SELECT LEN('C:\Documents and Settings\10015381.QINGDAOTRE\デスクトップ\評価結果100.xls') - CHARINDEX('\', REVERSE('C:\Documents and Settings\10015381.QINGDAOTRE\デスクトップ\評価結果100.xls')) + 2
返回@str中以@spl为截点的第@index个字符串 IF OBJECT_ID('split', 'fn') IS NOT NULL Drop function split GO create function split ( @str varchar(2000), @spl varchar(2)=',', @index int ) returns varchar(20) as begin declare @table table ( id int identity(1,1), textstr varchar(200) ) declare @dang int,@next int,@text varchar(200) set @dang=1 while @dang<=len(@str) begin set @next=charindex(@spl,@str,@dang) if @next=0 or @next is null set @next=len(@str)+1 set @text=substring(@str,@dang,@next-@dang) insert into @table values(@text) set @dang=@next+1 enddeclare @return varchar(20) select @return=textstr from @table where id=@index return @return end goprint dbo.split('cxn,fdwer,wercv,sdf,345,h,fgfg,5,fg',',',3)
set @s='“C:\Documents and Settings\10015381.QINGDAOTRE\デスクトップ\評価結果100.xls”'
select len(@s) - charindex('\',REVERSE (@s))+1-----------
54(1 行受影响)
SELECT LEN(@STR)-CHARINDEX('\',REVERSE(@STR))+1
没调试环境,试一下对不对
DECLARE @str varchar(100)
SET @str = 'C:\Documents and Settings\10015381.QINGDAOTRE\デスクトップ\評価結果100.xls'select reverse(substring(reverse(@str),0,charindex('\',reverse(@str))))/*
---------------
評価結果100.xls(1 行受影响)
*/
set @s='"C:\Documents and Settings\10015381.QINGDAOTRE\デスクトップ\評価結果100.xls"'
select substring(@s,len(@s)-charindex('\',REVERSE (@s))+2,len(@s))
- CHARINDEX('\', REVERSE('C:\Documents and Settings\10015381.QINGDAOTRE\デスクトップ\評価結果100.xls'))
+ 2
IF OBJECT_ID('split', 'fn') IS NOT NULL
Drop function split
GO
create function split
(
@str varchar(2000),
@spl varchar(2)=',',
@index int
)
returns varchar(20)
as
begin
declare @table table
(
id int identity(1,1),
textstr varchar(200)
)
declare @dang int,@next int,@text varchar(200)
set @dang=1
while @dang<=len(@str)
begin
set @next=charindex(@spl,@str,@dang)
if @next=0 or @next is null
set @next=len(@str)+1
set @text=substring(@str,@dang,@next-@dang)
insert into @table values(@text)
set @dang=@next+1
enddeclare @return varchar(20)
select @return=textstr from @table where id=@index
return @return
end
goprint dbo.split('cxn,fdwer,wercv,sdf,345,h,fgfg,5,fg',',',3)