March 15, 2005 at 4:28 pm
I tried to run a simple update statement with the REPLACE function to replace some portion of a text field and got an error, the field I am trying to update is a TEXT column. The update statement is simply this:
update table ABC
set text_column = REPLACE(text_column,'http://www.blah_blah', 'http://www.blah_blah2')
The other problem is that the occurence of this URL that I need to replace is not at the same place in each col.
The error - I get when I run the update statement above is:
Server: Msg 8116, Level 16, State 1, Line 2
Argument data type text is invalid for argument 1 of replace function.
So looks like this function is not supported in a text field.
Please help
Thanks,
Vik.
March 16, 2005 at 1:42 am
I made it work by casting the text column to varchar.
I presume that you are dealing with url that are no longer than 8000 bytes and so casting the text to varchar is not a problem.
On the other hand, what induced you to define an url as a text data type? Text data types are stored in their data pages. I think you need to define it as varchar.
update table ABC
set text_column = REPLACE(cast(text_column as varchar(8000),'http://www.blah_blah', 'http://www.blah_blah2')
Cheers,
Ben
March 16, 2005 at 1:46 am
I added the closing parenthesis to the CAST function....
I made it work by casting the text column to varchar.
I presume that you are dealing with url that are no longer than 8000 bytes and so casting the text to varchar is not a problem.
On the other hand, what induced you to define an url as a text data type? Text data types are stored in their data pages. I think you need to define it as varchar.
update table ABC
set text_column = REPLACE(cast(text_column as varchar(8000)),'http://www.blah_blah', 'http://www.blah_blah2')
Cheers,
Ben
March 16, 2005 at 2:48 am
If the URL to be updated is guranteed to be found within the first 8000 chars, this will work. If the text column may be larger, you might need to look at UPDATETEXT instead.
/Kenneth
March 16, 2005 at 7:38 am
Thanks for your replies guys. Actually its a messed up column, not sure who did this as its been like that for a long time. Field Stores a whole lot of text data and the URL that needs to be replaced is buried in there somewhere. So the cast might not work - I thought of the using cast earlier too but figured it would not work. Like Kenneth said, I am going to have to look into using the UPDATETEXT instead.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply