请教一个字符串截取的问题，分立结在线等

[shyuan]

我有个备份文件：E:\MSSQL\BACKUPS\DMS\MAB-MACHINE-001\MEV_DB_Log_ISP\FULL\A.BAK

想截取“倒数第三个”斜杠\后的内容，也就是得到 “\MEV_DB_Log_ISP\FULL\A.BAK” 字符串

因为“MEV_DB_Log_ISP” 是可变的，故无法确定长度。请教这个SUBSTRING应该怎么写？

[SQL77]

循环拆分

[jwdream2008]

Up!

[navy887]

declare @a varchar(200)
set @a='E:\MSSQL\BACKUPS\DMS\MAB-MACHINE-001\MEV_DB_Log_ISP\FULL\A.BAK'
select substring(@a,37,len(@a)-37)

[xiequan2]

declare @s varchar(500)
set @s='E:\MSSQL\BACKUPS\DMS\MAB-MACHINE-001\MEV_DB_Log_ISP\FULL\A.BAK'

select   right(@s,charindex('\',reverse(@s),charindex('\',reverse(@s),charindex('\',reverse(@s))+1)+1))

/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\MEV_DB_Log_ISP\FULL\A.BAK

(1 行受影响)


*/

[navy887]

总长度减去前边37位的长度即可。

[SQL77]

DECLARE @STR VARCHAR(500)
DECLARE @RESULT VARCHAR(500)
SET @STR='E:\MSSQL\BACKUPS\DMS\MAB-MACHINE-001\MEV_DB_Log_ISP\FULL\A.BAK'
SET @STR='SELECT COL ='''+REPLACE(@STR,'\',''' UNION ALL SELECT ''')+''''
PRINT @STR

--CREATE TABLE #T (ID INT IDENTITY(1,1),COL VARCHAR(50))

INSERT #T EXEC(@STR)

SELECT * FROM #T

DECLARE @RESULT VARCHAR(500)
SELECT @RESULT=ISNULL(@RESULT+'\','')+COL FROM #T
 WHERE ID IN (SELECT TOP 3 ID FROM #T ORDER BY ID DESC)
SELECT '\'+@RESULT

                                                                                                                                                                                                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
\MEV_DB_Log_ISP\FULL\A.BAK

（所影响的行数为 1 行）

[SQL77]

E:\MSSQL\BACKUPS\DMS\MAB-MACHINE-001

如果你前面的不变,那更简单,变的话用我楼上的可以

[shyuan]

多谢各位，立刻结贴

[nianran520]

declare @s nvarchar(100)
declare @i int,@ii int
select @i = 1, @ii = 1
select @s = 'E:\MSSQL\BACKUPS\DMS\MAB-MACHINE-001\MEV_DB_Log_ISP\FULL\A.BAK'
select @s = reverse(@s)
while charindex('\',@s,@ii) > 0
begin
	set @ii = charindex('\',@s,@ii) + 1 
	set @i = @i+1
	if @i = 4
	begin
		select @s = reverse(substring(@s,1,@ii-1))
		break
	end
end
print @s
-----------------
\MEV_DB_Log_ISP\FULL\A.BAK

[jwdream2008]

declare @a nvarchar(100)
set @a='E:\MSSQL\BACKUPS\DMS\MAB-MACHINE-001\MEV_DB_Log_ISP\FULL\A.BAK'
select reverse(left(reverse(@a),charindex('\', stuff(stuff(reverse(@a),charindex('\', reverse(@a)),1,' '),charindex('\',stuff(reverse(@a),charindex('\', reverse(@a)),1,' ')),1,' '))))