April 13, 2017 at 8:35 am
Hi Folks,
is this really the best way to extract the file name from a file path?
select replace(
reverse(
left(
reverse(physical_name)
, charindex('\', reverse(Physical_Name))
)
)
, '\', ''
) as FileName
from sys.master_files
order by 1 asc;
Sorry for the formatting.....somehow it didn't come out as I wanted...
Regards,
Kev
April 13, 2017 at 8:38 am
kevaburg - Thursday, April 13, 2017 8:35 AMHi Folks,is this really the best way to extract the file name from a file path?
select replace(
reverse(
left(
reverse(physical_name)
, charindex('\', reverse(Physical_Name))
)
)
, '\', ''
) as FileName
from sys.master_files
order by 1 asc;Sorry for the formatting.....somehow it didn't come out as I wanted...
Regards,
Kev
You could also skip the replace and do -1 after the charindex but this method is pretty good
April 13, 2017 at 8:43 am
Personally, I feel this might be a bit more concise:DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';
SELECT @FilePath AS FilePath,
RIGHT(@FilePath, CHARINDEX('\', REVERSE(@FilePath)) -1) AS FileName;
No need to REVERSE, LEFT, REVERSE then.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2017 at 8:45 am
Eirikur Eiriksson - Thursday, April 13, 2017 8:38 AMkevaburg - Thursday, April 13, 2017 8:35 AMHi Folks,is this really the best way to extract the file name from a file path?
select replace(
reverse(
left(
reverse(physical_name)
, charindex('\', reverse(Physical_Name))
)
)
, '\', ''
) as FileName
from sys.master_files
order by 1 asc;Sorry for the formatting.....somehow it didn't come out as I wanted...
Regards,
KevYou could also skip the replace and do -1 after the charindex but this method is pretty good
Hi there Eirikur,
thanks for the comment. I really thought there might be a better and easier way to do it....this took me an age to work out!
Now I remember why I have avoided development for so Long... rotfl
April 13, 2017 at 8:47 am
Thom A - Thursday, April 13, 2017 8:43 AMPersonally, I feel this might be a bit more concise:DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';
SELECT @FilePath AS FilePath,
RIGHT(@FilePath, CHARINDEX('\', REVERSE(@FilePath)) -1) AS FileName;
No need to REVERSE, LEFT, REVERSE then.
It is more readable as well I think.....cheers Thom
April 13, 2017 at 10:45 am
If you have the infamous DelimitedSplit8k stashed somewhere, it becomes trivial
DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';
SELECT TOP 1
FileName = split.Item
FROM dbo.udfDelimitedSplit8K(@FilePath, '\')split
ORDER BY split.ItemNumber DESC;
April 13, 2017 at 1:27 pm
I like to code to handle the case where there's no '\' in the data, to avoid the dreaded "Invalid length passed to RIGHT function."
DECLARE @FilePath VARCHAR(500) = 'My File Name.pdf';
SELECT @FilePath AS FilePath,
RIGHT(@FilePath, CHARINDEX('\', REVERSE('\' + @FilePath)) -1) AS FileName;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 27, 2017 at 12:44 am
Phil Parkin - Thursday, April 13, 2017 10:45 AMIf you have the infamous DelimitedSplit8k stashed somewhere, it becomes trivial
DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';
SELECT TOP 1
FileName = split.Item
FROM dbo.udfDelimitedSplit8K(@FilePath, '\')split
ORDER BY split.ItemNumber DESC;
Hi Phil,
what is the DelimitedSplit8k? That is really new to me....
Regards,
Kev
April 27, 2017 at 1:51 am
kevaburg - Thursday, April 27, 2017 12:44 AMPhil Parkin - Thursday, April 13, 2017 10:45 AMIf you have the infamous DelimitedSplit8k stashed somewhere, it becomes trivial
DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';
SELECT TOP 1
FileName = split.Item
FROM dbo.udfDelimitedSplit8K(@FilePath, '\')split
ORDER BY split.ItemNumber DESC;Hi Phil,
what is the DelimitedSplit8k? That is really new to me....Regards,
Kev
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 27, 2017 at 8:11 am
SELECT
--SUBSTRING(orignal file name,Last Char Index Of '\', FileName Length)
SUBSTRING( physical_Name,(LEN(physical_Name)-CHARINDEX('\',REVERSE(physical_Name))+2),LEN(physical_Name))
from sys.master_files
order by 1 asc;
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy