need to separate file path from file name

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

SQL Server 2000 Forums

Transact-SQL (2000)

need to separate file path from file name

Author

Topic

kbotar
Starting Member

4 Posts

Posted - 2006-06-21 : 07:50:04

Hello

I need to write a stored procedure for sql server 2000 where i separate the filepath and the filename in a string
(e.g. \\jordan.eur.emigrp.com\d\documents\cvs\docs\JA888713.txt)
I need to find the last "\" in a string, so I know that my filepath is \\jordan.eur.emigrp.com\d\documents\cvs\docs\ and my filename is JA888713.txt. Once I have this, I need to replace the old filepath with the new filepath so the string becomes
C:\Temp\JA888713.txt

Please note, theere are 50000 filepaths in the table and their length varies. How can i find the filepath without the filename and replace it?

Thanks

Kati

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-06-21 : 08:30:53

Hi Kati. Try this:

-- Declare variable to store new path
DECLARE @NewPath VARCHAR(32)

-- Create table variable to store test data
DECLARE @tmp TABLE (FilePath VARCHAR(255))

-- Populate table variable with test data
INSERT INTO @tmp VALUES('C:\one\two\three\file_one.txt')
INSERT INTO @tmp VALUES('D:\four\five\file_two.csv')
INSERT INTO @tmp VALUES('E:\five\file_three.dat')

-- Set new path
SET @NewPath = 'C:\Temp'

-- Select desired resultset
SELECT  STUFF(t.FilePath, 1, LEN(t.FilePath)-CHARINDEX('\', REVERSE(t.FilePath)), @NewPath) 
FROM   @tmp AS t

Mark

kbotar
Starting Member

4 Posts

Posted - 2006-06-21 : 08:42:26

Wow Mark you are a star! Thanks so much

Kati

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-06-21 : 08:52:13

No problem Kati!

Mark

kbotar
Starting Member

4 Posts

Posted - 2006-06-21 : 10:00:12

If I want to first check that the new and the old folders are different do i need a cursor? I only want to update the files if for example C:\one\two\three differs from C:\Temp.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-21 : 10:07:08

ADD a where clause

where	LEFT(t.FilePath, LEN(t.FilePath)-CHARINDEX('\', REVERSE(t.FilePath))) <> @NewPath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources