Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KenP
Starting Member
16 Posts |
 Posted - 2008-10-08 : 13:23:05
|
| Ok ladies and gents... I am super frustrated.What I have is a database that runs our site. We have a table that has all of our page information in it. There is about 60k + rows of information. This information is loaded into a template and displayed as an ASPX page on our site.there are about 3-4 columns in this table that contain basic HTML and javascript text. these columns are Text(16)we have had a site programming change that will require me to go through and edit every link in our HTML. I can fix this with a find a replace but I am having major issues with every way i try to do this.I have tried to find a good script to find and replace the text i need using a query... none seem to work or data gets truncated.I try exporting to an Excel spreadsheet and i run into a few problem. The file that I export, when i try opening it in excel, give me a message:Excel found unreadable content in 'file.xls'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click yes.and of course when I click yes, data is missing.and then I found out, that even if I COULD get certain columns exported (ones that didnt cause errors, or have missing data) I still couldnt use Excel to find and replace as the "Formula is to long"PLEASE help.I dont want to have to edit each page by hand using our web admin.Ken |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-08 : 14:46:04
|
| SELECT REPLACE(<YOU TEXT CLOUMN>,'<URL YOU WANT TO CHANGE>','<NEW URL>') FROM <YOUR TABLE>WHERE <YOUR TEXT COLUMN> LIKE '%<URL YOU WANT TO CHANGE>%' |
 |
|
|
KenP
Starting Member
16 Posts |
Posted - 2008-10-08 : 15:25:39
|
| SELECT REPLACE(cat_description3, 'categories', '/categories')FROM CategoriesTESTgives the error:Argument data type text is invalid for argument 1 of replace function. |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-08 : 15:28:00
|
| you can't use replace function for TEXT data type. sounds like you'll need to backup your table and use UPDATETEXT() |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-08 : 15:33:19
|
| This article should give you an good idea on how to replace in text datatype:http://www.sqlteam.com/article/search-and-replace-in-a-text-column |
|
|
|
|
|
|
find and replace or export or something!