DECLARE @StringA varchar(100), @pos int
SET @stringA = 'DirectoryA\DirectoryB\FileName'

-- Where is the last '\'?

SET @pos = LEN(@StringA) - CHARINDEX('\',REVERSE(@StringA))
SELECT @pos

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DECLARE @SomeFile VARCHAR(255)

SET @SomeFile = 'C:\WINDOWS\system32\FileName.xls'

SELECT RIGHT( @SomeFile, CHARINDEX( '\', REVERSE( @SomeFile ) + '\' ) - 1 ) 

SELECT FieldA, COALESCE(RIGHT(FieldA, NULLIF(CHARINDEX('\', REVERSE(FieldA)) - 1, -1)), FieldA, '') AS Filename
FROM (
   SELECT 'DirectoryA\DirectoryB\DirectoryC\FileName1' AS FieldA UNION ALL
   SELECT 'DirectoryA\DirectoryB\FileName2' UNION ALL
   SELECT 'FileName3' UNION ALL
   SELECT 'DirectoryA\DirectoryB\' UNION ALL
   SELECT '' UNION ALL
   SELECT NULL
) TableA