updatetext help

  • 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!

  • 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

  • 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.

  • 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