Extracting the Filename from the Path

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting the Filename from the Path

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2013-11-08 : 09:52:01

Hi,

I have to extract only the filename from the below filepath.
The first part \\prodserver\export always remains the same. A new folder is created \\prodserver\export \20071001, \\prodserver\export \200710012 etc each time the file is dropped . I want to extract only the part after that namely 20071001-200001-e430f8fd-PREC272.wma and like wise and dump it into a table.

\\prodserver\export\20071001\20071001-200001-e430f8fd-PREC272.wma
\\prodserver\export\20071002\20071002-013203-d00b2007-PREC120.wma
\\prodserver\export\20071002\20071002-032038-18381b92-PREC207.wma
\\prodserver\export\20071008\20071008-144758-16359a7b-PREC224.wma
\\prodserver\export\20071009\20071009-170426-4ed3535c-PREC97.wma

Thank you for your help,
Petronas

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-08 : 10:13:43

>>The first part \\prodserver\export always remains the same

Then just:
select replace(<columnName>, '\\prodserver\export\', '')

Be One with the Optimizer
TG

Petronas
Posting Yak Master

134 Posts

Posted - 2013-11-08 : 11:47:52

Thanks TG! I am sorry if I am slow in following . I tried the below and it came up as blank.
Appreciate your help. Thank you

DECLARE @PathFile varchar(512)
SET @PathFile = '\\prodserver\export\20071001\20071001-200001-e430f8fd-PREC272.wma'

create table #Temp_name
( Name varchar(512)
)

Select replace(Name, '\\prodserver\export\', '')
from #Temp_name

drop table #temp_name

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-11-08 : 12:57:26

try this:

[CODE]

DECLARE @PathFile varchar(512)
SET @PathFile = '\\prodserver\export\20071001\20071001-200001-e430f8fd-PREC272.wma'

create table #Temp_name
( Name varchar(512)
)

insert into #Temp_name Values (@PathFile);

Select replace(@PathFile, '\\prodserver\export\', ''), Name
from #Temp_name

Select replace(Name, '\\prodserver\export\', ''), Name
from #Temp_name

drop table #temp_name

[/CODE]

Petronas
Posting Yak Master

134 Posts

Posted - 2013-11-08 : 13:24:10

Thank you MuMu88, it worked great. Appreciate your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-08 : 14:10:56

Petronas,
You saw the problem right? You just never inserted that path value into your #temp_name table