SQL Find & Replace scripts that WORKS!

[zanzor]

Another title to add the the solo business owner/web developer of an able commerce site... SQL Programmer...

After doing some research i found some GREAT scripts and got them to work with able commerce, they have saved HOURS of labor, compaired to changing the data in able.
This are copied into your Microsoft SQL Server managment Studio as a NEW QUERY after you open up the database.
******************************************************************
MAKE BACK UP FIRST IN CASE YOU MESS IT ALL UP!!!!!!
*******************************************************************

a simple find and replace , this i use to correct a type in the digital goods fields in 50,000 items

Code: Select all

update [YOURDATABASENAME].[dbo].[ac_DigitalGoods]
set ServerFileName = REPLACE(ServerFileName, 'C:\Inetpub\vhosts\gpsmanual.com\httpdocs\manualsink_com\App_Data\DigitalGoods\88manuals88', '88manuals88');

here is one to ADD data to a field that already has some data in it

Code: Select all

UPDATE [YOURDATABASENAME].[dbo].[ac_Products]
SET Name=Name + ' text to add' 

but what happens with you have NOT put anythign in there yet, it has the NULL Field you have to use this sql

Code: Select all

UPDATE [YOURDATABASENAME].[dbo].[ac_Products]
SET SearchKeywords='words to add' where SearchKeywords is null

now here is a SMALL PROBLEM with that last code when you use it with the SearchKeywords field it will put text into that field but not in the proper format.

As you may know to have the keyword correct in able it has to be like this
word1
word2
word3

but when you view that data in SQL Server it shows it as:
word1 word2 word3

That is a DOUBLE SPACE between the words.
so ive tried putting a double space
..putting <BR>
..putting <BR />
..putting /r/n

when trying any of those thing between the words1 ,2 etc it will change the text in able but does NOT make it do a line break
shows it as
word1<br />word2<br />word3

and able CAN NOT translate this into different keywords..

any advice on this?

Thanks
And I hope some people get use of the other 2 SQL Scripts, they have saved me alot of time and money!

Tim Ross