The most convenient way to do string replacements across the entire database?
Edit: The technologies used are asp.net for the CMS and MSSQL for the database.
I want to move a project from development server to live server. The CMS I am using stores some urls as absolute paths rather than relative ones, which breaks a lot of images.
The short-cut URLs are scattered across multiple tables throughout the database. Some of them are in specific fields (like "source" or "url"), but most of them are only part of large text fields (html content in cms).
Now I'm looking for an easy and quick way to "fix" URLs before / when moving a site.
All urls have the same structure, so it basically boils down to search-replace, however when trying to find a simple search-replace for partial strings in SQL I was overwhelmed with code walls containing multiple loops, etc.
So my current plan is to export the entire database (like mysqldump), load it into a text editor, search, replace, save, import.
This seems to be the most convenient / least difficult solution for this particular problem for me. However, I rarely deal with SQL directly, so maybe I'm missing something? If there is a better and / or more elegant way, please enlighten me.
source to share
Hope this helps you,
The idea here is to iterate over all columns (character columns only) in all tables and update the column with a statement UPDATE
containing a statement REPLACE
.
Sql:
Declare @sysTab table (id int identity(1,1),[object_id] int)
Insert into @sysTab
Select object_id from sys.tables Where type = 'U'
Declare @ts int = 1, @te int = (Select Count(*) From sys.tables)
Declare @cs int ,@ce int,@object_id int, @sql varchar(max)
While @ts <= @te
Begin
Set @cs = 1
Set @object_id = (Select [object_id] From @sysTab Where id = @ts)
Set @ce = ( Select Count(*)
from sys.columns C
join sys.types ty On C.system_type_id = ty.system_type_id
where ty.name in ('char','nchar','ntext','nvarchar','text','varchar','nchar')
And object_id = @object_id)
While @cs <= @ce
Begin
Set @sql = 'Update A Set A.' + (Select name from sys.columns c where c.object_id = @object_id) +' = replace(A.'+ (Select name from sys.columns c where c.object_id = @object_id) + ',''URL'',''newURL'') From' + (Select name from sys.tables t where t.object_id = @object_id) + 'A'
Exec(@sql)
Set @cs = @cs + 1
End
Set @ts = @ts + 1
End
source to share