Hey:
I'm not sure where you're going with this. From your example, you are searching for columns [content] in a specific table named [contentmanager]. If you want to search *all* columns for *all* tables in a database for specific content, you'd need to build a query using the system tables, checking that the column is a string 'type' column (char, varchar, etc), and then apply the select--and that's just to list all of the columns in all of the tables that match.
Perhaps I'm looking too far into what you're doing. If you wanted to update values in a specific column from a specific table in a database, then using your example it would be:
UPDATE dnn].[dbo].[ContentManager]
SET [Content = '/Portals/0/'
WHERE [Content] LIKE '%/
oldVirtualFolderName/Portals/0/%'
But I don't think that's what you're talking about. It's pretty complex to build a stored proc that will replace values throughout an entire database. The place to ask this question is on www.experts-exchange, in the MSSQL category. An expert there would be able to help ya.
Hope that helps.
Posted By sjennings on 14 Feb 2007 12:01 PM
I recently made a change to a portal path (I am simplifying this) and I need to do a global find and replace in SQL to change the path to the portal folder. I don't think alternative methods to changing the portal path will work because this situation is a bit complicated and I am almost certain this is the only way for me to do this.
That being said, does anyone know how to do a global find and replace for a substring in a SQL database?
I want to search for
“/oldVirtualFolderName/Portals/0/”
and replace it with “/Portals/0/”
SELECT [Content]
FROM [dnn].[dbo].[ContentManager]
WHERE [Content] LIKE '%/
oldVirtualFolderName/Portals/0/%'
will return a list of relevant fields but I do not know how to apply an update to the substring.
Can anyone tell me how this is possible?