REPLACE() within a TEXT column gives error

Question

Post reply

REPLACE() within a TEXT column gives error

Vik Nat

Hall of Fame

Points: 3930
More actions
March 15, 2005 at 4:28 pm

#66983

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Benny Lopez Ten Centuries Points: 1111 More actions · Answer 1

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

Benny Lopez Ten Centuries Points: 1111 More actions · Answer 2

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Kenneth Wilhelmsson

SSC-Dedicated

Points: 30043

March 16, 2005 at 2:48 am

#547218

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

Vik Nat Hall of Fame Points: 3930 More actions · Answer 4

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.