Argument data type text is invalid for argument 1 of replace function

[TechRick]

I receive this error message when trying to update (remove comma's in the data):
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

I noticed that the data type for this column is T_MEMO
and I can't find anything regarding this data type.

Here is my statement:
UPDATE address
SET address.street_address = (REPLACE(address.street_address,',',' '))

Any thoughts/comments?

Thanks,
TechRick

 

[swampBoogie]

You should change the datatype to varchar(40) or something like that. Text is used for storing large objects.

 

[SQLSister]

Could it be a user defined type? It is not a standard type that comes with SQL server. Check the user defined type to see what the base datatype is. If it is a text field and not a character field it needs to be handled differently. Look up WRITETEXT in books online for how to update a text field.

 

[tlbroadbent]

Addresses certainly don't need to be data type text. If you can't fit an address in an 8000 character varchar column, something is definitely wrong.

In the meantime, you could do as suggested and convert the column as in this query. Thisd will convert the column to varchar, execute the REPLACE function and implicitly convert it back for storage in teh table.

UPDATE address SET street_address =
(REPLACE(convert(varchar(8000),street_address),',',' '))
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.