Need help replacing all instances of incorrect text 1

[markdmac]

I've been asked to step in and take over a migration project. The company that setup the migration tools transposed letters in the domain name. Can anyone assist me with SQL update code to search all databases for the text LSV\ and replace it with LVS\?

Regretfully I don't know what field this will be in and the database has several hundred tables to search, so I am hoping to be able to just plug in the database name which is QMM and have code walk the database.

I hope that helps.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[gmmastros]

Mark,

If you run the query below, it will generate all of the replace statements you will need, but not actually run the replace statements. When I run the code below on my database, I get 1840 rows in the output. You could then copy/paste the output to a new query window, verify the statements (removing any you don't want to run) and then execute them. I hope this is enough to be helpful.

Select  'Update [' + table_schema + '].[' + table_name + '] ' +
        'Set [' + column_name + '] = Replace([' + column_name + '], ''LSV\'', ''LVS\'') ' +
        'Where [' + column_name + '] Like ''%LSV\%'''
From    Information_Schema.Columns 
where   data_type Like '%char%' 
        and character_maximum_length > 3

Note this this code limits the columns to just those that are varchar, nvarchar, char and nchar and only operates on columns where the defined max size is greater than 3 characters.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dhulbert]

Mark.

George's script above will give you any columns with the appropraiate text in the column name.

Are you looking for that or do you need to find the text in individual records in a table?



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[gmmastros]

dhulbert,

You are mistaken. The query I show above does not look for the appropriate text in the column name. Instead, it generates a sql query for each column that has a string type with maxlength exceeding 3 characters.

When I run the code on my database, the output looks like this"

[tt]
Update [dbo].[Address]
Set [StreetName] = Replace([StreetName], 'LSV\', 'LVS\')
Where [StreetName] Like '%LSV\%'

Update [dbo].[Address]
Set [StreetType] = Replace([StreetType], 'LSV\', 'LVS\')
Where [StreetType] Like '%LSV\%'

Update [dbo].[Address]
Set [Suffix] = Replace([Suffix], 'LSV\', 'LVS\')
Where [Suffix] Like '%LSV\%'

Update [dbo].[Address]
Set [Address2] = Replace([Address2], 'LSV\', 'LVS\')
Where [Address2] Like '%LSV\%'

Update [dbo].[Address]
Set [City] = Replace([City], 'LSV\', 'LVS\')
Where [City] Like '%LSV\%'
[/tt]

I added the line breaks manually so that the output would be clearer to understand.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dhulbert]

Apologies George.

Quick skim first thing in the morning before coffee.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[markdmac]

No worries guys. I ended up having to check each table manually but got all instances fixed. The code that was generated did not seem to identify tables that actually had the offending value.

I hope that helps.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.