March 30, 2012 at 10:25 am
I have a table in a SQL Server database that has 8000 rows that need to be updated with a new drive locations. The current entry in record for the “path” column is similar to:
D:\fsaATLAS\YourUniversityName\SevisDownloads\somepdfname.n (The “somepdfname.n” changes with each record)
I would like to change only part of the drive and folder portion of each “path” record to:
“E:\fsaATLAS\BostonUniversity”
Thus the result for all 8000 records would be changed to a format like this:
E:\fsaATLAS\BostonUniversity\SevisDownloads\somepdfname.n
Can this be performed with a T-SQL statement? I dread doing 8000 records by hand. Thank you in advance for any information.
March 30, 2012 at 11:02 am
Can be done!
Will the string always follow this pattern:
drive:\rootfolder\firstfolder\secondfolder\filename.extension
??
March 30, 2012 at 11:04 am
yes... all 8000 records have that pattern. I need to change the drive letter and the first two folders, but retain the reamining information in each record. Thank you again for your time and information!
March 30, 2012 at 12:40 pm
This , may be??
DECLARE @Replacement VARCHAR(100)
SELECT @Replacement = 'E:\fsaATLAS\BostonUniversity\'
DECLARE @Table TABLE
(
FileFolder VARCHAR(200)
)
INSERT @Table
SELECT 'D:\fsaATLAS\YourUniversityName\SevisDownloads\somepdfname.n'
SELECT T.FileFolder
,NewLoc = REVERSE(LEFT(CrsApp.Rvrs, CHARINDEX('\', CrsApp.Rvrs , CHARINDEX('\',CrsApp.Rvrs)+1 )-1) + REVERSE(@Replacement))
FROM @Table T
CROSS APPLY ( SELECT REVERSE(T.FileFolder) ) CrsApp (Rvrs)
March 30, 2012 at 1:06 pm
Thank You SSCommitted!!
March 30, 2012 at 1:53 pm
If the value you are trying to replace is exactly the same right now you could just use a replace.
update @Table set FileFolder = REPLACE(FileFolder, 'D:\fsaATLAS\YourUniversityName\', 'E:\fsaATLAS\BostonUniversity\')
The version ColdCoffee posted is a bit more flexible but if the values are consistent a simple replace would work. It depends on your data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply