Find and replace

Other SQL Server Topics
1

OK, pretty quick question. I am looking to move some folders to save space in a local F:/ Drive, however, the SQL tables I have, has links to files in that drive. For example, a table will have a column "Path" and have the following "F:\Energy Brokerage\Client\Suppliers\Suppliername\Utility\xxx.pdf". If I update this path to "F:\Energy\Client\Suppliers\Utility\xxx.pdf" Is there a way to update all of the "Path" column to find and replace just the path name change? (Document name is remaining the same)

Thanks

2

I do not know of a find and replace but you could find it by searching the procedures then modify them.

SELECT 
    O.Name AS ModuleName, 
    CASE O.Type 
        WHEN 'P'  THEN 'USP' WHEN 'FN' THEN 'UDF' 
        WHEN 'FS' THEN 'UDF' WHEN 'FT' THEN 'UDF' 
        WHEN 'TR' THEN 'TRIG' END AS ModuleType, 
    M.[definition] AS ModuleText, 
    O.create_date, 
    O.modify_date 
FROM sys.objects           O 
INNER JOIN sys.sql_modules M ON O.object_id = M.object_id
WHERE O.is_ms_shipped = 0 
  AND O.[type] in ('P','FN','FS','FT','TR')
AND M.[definition] LIKE 'your old path';

I hope this helps or at least gives you an idea.

1 Like
3

Thanks djj,
Just an idea to make it more simple, would the following work?

UPDATE table SET field1 = replace(field1, ‘partToFind’, ‘partToReplaceWith’) WHERE conditions….e.g.

if field1 = F:\energy\folder1\document.doc
and I do

SET field1 = REPLACE(field1, ‘energy’, ‘new folder’) I will get

F:\new folder\folder1\document.doc?