Manage File Path In Sql

Extracting file name from full file path

Suppose we have saved our images with their path name in sql as /root/subroot/image/image.jpg
And we need to extract only image name without their path.

For this we are going to use two to three inbuilt functions as SUBSTRING(),CHARINDEX(),REVERSE()

 SUBSTRING(string, start postion, End positions )  --  For Splitting Strings

 CHARINDEX("Expression", string) --  For Getting Specified Postion

 REVERSE(string)     --   Usually used to reversed strings.

 CHARINDEX('/',REVERSE(@Path))   --   Finding last "/" string position in string

 Using it with string "/root/subroot/image/image.jpg" gives 10 th position.
LEN(@Path)  -- Total length of above string is 29.
 LEN(@Path) - CHARINDEX('/',REVERSE(@Path))  --  (29 - 10)   -->> which gives 19


SUBSTRING (@Path,1 , LEN(@Path) - CHARINDEX('/',REVERSE(@Path)) )

gives all part leaving image name from string. ex- It gives "/root/subroot/image"

Then we are using replace(string, part of string to replace, replace with)
replace(@Path, @splitted+'/','')  --  replace(' /root/subroot/image/image.jpg', '/root/subroot/image'+ '/', '')

By using this we got exact image name.

We can use it as a function to be clear and concise as given below.

 Create FUNCTION SplitImageFromPath
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @splitted NVARCHAR(MAX)
   DECLARE @result NVARCHAR(MAX)

     
 set  @splitted = SUBSTRING (@Path ,1 , LEN(@Path) - CHARINDEX('/',REVERSE(@Path)) )

 set @result =  replace(@Path, @splitted+'/','')

    RETURN @result
END