SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extracting the Filename from the Path
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Petronas
Posting Yak Master

133 Posts

Posted - 11/08/2013 :  09:52:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/08/2013 :  10:13:43  Show Profile  Reply with Quote
>>The first part \\prodserver\export always remains the same

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

Be One with the Optimizer
TG
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 11/08/2013 :  11:47:52  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 11/08/2013 :  12:57:26  Show Profile  Reply with Quote
try this:



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 
 

Edited by - MuMu88 on 11/08/2013 13:01:24
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 11/08/2013 :  13:24:10  Show Profile  Reply with Quote
Thank you MuMu88, it worked great. Appreciate your help.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/08/2013 :  14:10:56  Show Profile  Reply with Quote
Petronas,
You saw the problem right? You just never inserted that path value into your #temp_name table

Be One with the Optimizer
TG
Go to Top of Page

Petronas
Posting Yak Master

133 Posts

Posted - 11/08/2013 :  16:49:56  Show Profile  Reply with Quote
I agree TG.. It is Friday :-) Thanks for your help with it !
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 11/11/2013 :  04:00:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
The Generic approach would be

select right(name,charindex('\',reverse(name))-1) from #Temp_name

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000