stripping just the filename from a string in sql by looking at the '/' dash character

Question

I would like to know how to accomplish getting just the file name from the below

C:\Users\avs\Desktop\Testing\Text Files\TargetFolder\PN005337.RCS

the PN005337.RCS file name can vary in name and length. However the only definitive way of capturing it would be looking at the last '\' and then bringing back any thing after the very last '\'.

Anyway to do that in sql. This is a column in sql server, but the report owner just wants to see the name.

I am doing this in SSIS so either solution would be great in an expression or in SQL.

Thank you

Possible duplicate of [Parse file name and path from full path](http://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path) — JohnHC, Oct 12 '16 at 08:48

Robert Kock · Answer 1 · 2016-10-12T09:05:46.243

1

You could determine the index of the last slash by first REVERSE the path and then find the slash by means of CHARINDEX.
Finally you extract the filename applying to the original path the function RIGHT using the found index.

The expression would be RIGHT(path, CHARINDEX('/', REVERSE(path)) - 1)

edited Oct 12 '16 at 09:05

answered Oct 12 '16 at 08:51

Robert Kock

5,795
1
12
20

Thank you Robert, this gives me exactly what I need to know. Any chance you can give it in SSIS as en expression just for my benefit as well? – abs786123 Oct 12 '16 at 08:56
Sorry in SSIS, as charindex isnt an option in SSIS expression – abs786123 Oct 12 '16 at 09:10
It's been years the last time I used SSIS. Sorry about that. – Robert Kock Oct 12 '16 at 09:14
No worries, this gives it what i want anyway – abs786123 Oct 12 '16 at 09:40

score 0 · Answer 2 · answered Oct 12 '16 at 09:06

A case for CROSS APPLY to make intermediate computations

declare @t table (full_path varchar(max));
insert @t values 
 ('C:\Users\avs\Desktop\Testing\Text Files\TargetFolder\PN005337.RCS'), 
 ('C:PN005337.RCS'), 
 ('PN005337.RCS');


SELECT full_path, 
    CASE WHEN pback>=0 THEN RIGHT(full_path, pback) 
         WHEN pcol>=0 THEN RIGHT(full_path, pcol) 
         ELSE full_path
         END as fname
FROM  @t
CROSS APPLY (
    SELECT pback= CHARINDEX('\', REVERSE(full_path)) -1, pCol = CHARINDEX(':', REVERSE(full_path)) -1
    ) pp

stripping just the filename from a string in sql by looking at the '/' dash character

2 Answers2