October 6, 2011 at 1:32 pm
I have searched around the forum and tried a few times on my own, but we have a need to quickly make a change so I would appreciate any help.
Running SQL 2005, i have a table with a text field. This field will often reference 'server3' in a url string, and I would like to simply replace those occurences with 'ps'.
My table name is f00165 and the field name is gdtxvc. I tried this command:
updatetext f00165 set gdtxvc=replace(gdtxvc, 'server3' ,'ps')
but this gets an invalid syntax error, so I must be way off. I need to get this change made today and our production users are impacted by the incorrect reference.... :sick:
If someone could point me in the right direction, I really appreciate it!
October 6, 2011 at 2:24 pm
You're mixing the syntax for UPDATE and UPDATETEXT. You need to use one or the other.
text fields are being deprecated. You should consider changing it to a varchar(max) field.
I don't think that Replace works with text objects.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 6, 2011 at 2:44 pm
I'm going to use a temp table and convert the text field to a varchar(max) field, update the field in the temp table, then update the original table with the new value.
I think this will do it.
October 7, 2011 at 10:06 am
robert.daniel (10/6/2011)
I'm going to use a temp table and convert the text field to a varchar(max) field, update the field in the temp table, then update the original table with the new value.I think this will do it.
Seems like that would be a good time to change to varchar(max). Just change the datatype first, then update.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply