Update path? - SQL Server Forums

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)

Update path?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-08-23 : 13:52:47

I have a field that has the file path in there. What I want to do is go in and check for certain characters from right to left and first character that it sees base on criteria, it will trim it, then update it with a new path.

Here's the example of what's in the field. I want to search right to left until the first '\' and then do a trim from there:

E:\Report\ACCT\VMDIBS.RPT
E:\Report\ACCT\RPT332.RPT
E:\Report\FIN\TR345D.RPT
E:\Report\HR\DP34012D.RPT
E:\Report\HR\DKEK908.RPT

After trim:

VMDIBS.RPT
RPT332.RPT
TR345D.RPT
DP34012D.RPT
DKEK908.RPT

I want that path to change to one that I created in a different drive. New path will be J:\NewReport\""
J:\NewReport\ACCT\VMDIBS.RPT
J:\NewReport\ACCT\RPT332.RPT
J:\NewReport\FIN\TR345D.RPT
J:\NewReport\HR\DP34012D.RPT
J:\NewReport\HR\DKEK908.RPT

I was thinking about creating a temp table, select the file name from doing a left trim where there's '\', then insert the file name into the temp table. After that, add the new path and append the file name to it, and then update.

Anyone any ideas how to go about doing this? I kind of have a feeling how to go about working on this, but not sure how to approach it.

Here's an attempt, but I'm lost when telling it to check from left to right and when it sees '\' then trim left and just give me the file name.


CREATE TABLE #TEMP1(
	PATH VARCHAR(30)
	)

INSERT INTO #TEMP1(
	PATH
	)

SELECT CASE WHEN CHARINDEX('\', R.PATH, LEFTTRIM ) THEN SUBSTRING(R.PATH, CHARINDEX('\',
R.PATH, 6)+1, 1) END AS FILE_PATH
FROM REPORT R

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-23 : 13:59:26

Are you aware that there is a REVERSE(string) function that returns a string in reverse.

Seems like you have all the remaining parts. You shouldn't need a temporary table to get this one solved.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-08-23 : 14:22:38

Thanks for the response, but i'm not sure how the Reverse(string) function will help. It will reverse the path, so I assume I can trim to the first back slash '\', then reverse it back.

Any ideas how to go about using it? Books online doesn't so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 02:17:12

See if this helps you

declare @s varchar(100)
Set @s='E:\Report\ACCT\VMDIBS.RPT'
select replace(replace(@s,'E:\','J:\'),'\Report\','\NewReport\')

Madhivanan

Failing to plan is Planning to fail

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-08-24 : 09:51:04

Thanks for the example. Before I go and make the changes a couple of questions.

1. So if I have about 100 path, I will need to go and change the path for each one where the parameter is right?

2. If I have more subfolders, can I just add them to the SELECT statement?

Example:

SELECT REPLACE(REPLACE(@S, 'E:\', 'J:\'), \Report\','\NewReport\', '\TEST\', '\FINALREPORT\')

Again thanks for the help. Appreciate it.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-08-24 : 09:55:42

Ignore question 2 that I have.

I tested it out and it will pull the current path only. It does not replace the current path with the new path that has define.

For example:

E:\Report\ACCT\VMDIBS.RPT

Still the same and does not change to "J:\NewReport\ACCT\VMDIBS.RPT"

Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 10:08:22

Yes like this

Did you execute the method I specified?

Madhivanan

Failing to plan is Planning to fail

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-08-24 : 10:15:03

Yes it works now. So I will need to create a temp table to store the string and then create an update to update the field?

Just curious if there's a way to have this done in one batch since we have 3 databases with over 90+ path for each one on those reports

Was thinking if it can do a search from right to left and first back slash it sees it will trim it. So E:\Report\ACCT\VMDIBS.RPT will get trim and only VMDIBS.RPT left. Then maybe use the replace function to have it replace and then update? But if there's no other way then I guess I will have to manually go and change it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 10:21:19

Try this

Update yourTable set Field=replace(replace(Field,'E:\','J:\'),'\Report\','\NewReport\')

Madhivanan

Failing to plan is Planning to fail

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-08-24 : 11:05:07

Thanks I was able to update the field. Will doing a long repetitive update.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources