Parse file name and path from full path

Question

I need to parse file name and file path from full path using SQL Query.

Eg. Fullpath - \SERVER\D$\EXPORTFILES\EXPORT001.csv

FileName        Path
EXPORT001.csv   \\SERVER\D$\EXPORTFILES\

Answer 1

Using REVERSE is easier to see

DECLARE @full_path VARCHAR(1000)
SET @full_path = '\\SERVER\D$\EXPORTFILES\EXPORT001.csv'

select REVERSE(LEFT(REVERSE(@full_path),CHARINDEX( '\',REVERSE(@full_path))-1)) as [FileName],
       replace(@full_path, REVERSE(LEFT(REVERSE(@full_path),CHARINDEX( '\',REVERSE(@full_path))-1)),'') as [FilePath]

Answer 2

select 
LTRIM(
RTRIM(
REVERSE(
SUBSTRING(
REVERSE(Filename),0,CHARINDEX('\',REVERSE(Filename),0))
)))
 from TblFilePath