Help using Replace() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help using Replace()

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-10-28 : 13:39:36

I am trying to use replace() within my update statement. For an example, lets say I am trying to replace 'xxx' with 'yyy' using the below query. When run, I get the following error: 'Argument data type text is invalid for argument 1 of replace function.'

Any thoughts.....?

update crb
set crb.description = replace(crb.description, 'xxx', 'yyy')
from contact_role_bridge crb
where crb.description like '%xxx%' and crb.description is not null and
(contact_role_id = 0x0000000000000085 or contact_role_id = 0x0000000000000086)

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-10-28 : 14:07:47

You can not use REPLACE on a field over 8000 bytes in length. That translates to 4000 characters, if you are using NTEXT. Since this looks to be only 2 items, you could just update the text fields in notepad, and outright replace the whole thing in the database.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-10-28 : 14:13:57

Thank Mcrowley, The number if characters in the description field are no more thank 100 characters.
I thought about using the Stuff() function but get the same error.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-10-28 : 16:08:45

Try this, then:


update crb
set crb.description = replace(substring(crb.description, 1, 8000), 'xxx', 'yyy')
from contact_role_bridge crb
where crb.description like '%xxx%' and crb.description is not null and
(contact_role_id = 0x0000000000000085 or contact_role_id = 0x0000000000000086)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 00:16:58

here's a method for getting replace functionality in text,ntext fields

http://www.sqlservercentral.com/articles/Miscellaneous/handlingthetextdatatype/985/

Also, just keep in mind that text,ntext etc are depricated features in sql 2005 so its better to use varchar(max),nvarchar(max) instead

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-10-29 : 07:02:38

Thanks to the both of you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:05:58

quote:
Originally posted by qman

Thanks to the both of you!

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources