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
SQL Server Portal 