SQL Server Portal

Recently, I was working on a legacy database and I came across an issue where I had a full file path of a document but the file name was not separated / isolated from the file path.

Let me create a sample to demonstrate it.

USE Tempdb
GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[File Path] VARCHAR(200)
)
GO
INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (1,'C:\Users\imran\Documents\worksheet.xlsx')

INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (2,'C:\Users\imran\image.png')

INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (3,'C:\Users\imran\Documents\Document.doc')
GO

Given below is a script that separates file name from file path.

SELECT
[ID]
,[File Path]
,SUBSTRING([File Path],LEN([File Path])
- CHARINDEX('\',REVERSE([File Path]))+2,LEN([File Path]))
AS [File Name]
FROM tbl_Sample
GO
--OUTPUT