REPLACE function in stored procedure to update text field?

Posted on 2003-03-03
Medium Priority
Last Modified: 2011-10-03
Hi all,

I get an error when I try to use the replace function on a text field:

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

I guess it only works for varchar etc so now I need to find a way to work around this. The application is a web based mailing list where users can enter text for the email message that is then personalised, such as "Dear [Firstname]". The error message above came from

UPDATE      #MessageSend
SET Person_Message = REPLACE(Person_Message, '[Firstname]', Person_Firstname)

I wrote my first stored procedure ever yesterday so I don't know whether I'm heading in the right direction! If I end up having to loop through character by character (how?), do you have any idea how many rows I can do this on before everything grinds to a halt? The emails will be a few paragraphs at most (it's an invitation to take part in a survey) and there will be maybe 5000 recipients.

Any help much appreciated. TIA

Question by:alexessory
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 18

Expert Comment

ID: 8056181
If it is only a few paragraphs then maybe it will fit into a varchar which can be 8000 chars. Then you can use

UPDATE      #MessageSend
SET Person_Message = REPLACE(convert(varchar(8000),Person_Message), '[Firstname]', Person_Firstname)

If not then you will need to loop through 8000 chars at a time (or 7000 to allow for replace characters) using substring and updatetext.

Accepted Solution

cherrypie earned 150 total points
ID: 8056791
hi there

use the substring() function to essentially convert the text field to varchar and then replace whatever string you need


replace( substring(person_message,1,datalength(person_message)), '[Firstname]', Person_Firstname)


Author Comment

ID: 8060790
Thank you both - I think I may be able to get away with the varchar(8000) (actually nvarchar(4000) but that's another story!) but in case the text turns out to be longer, can I ask you for a code snippet to show how to loop through a longer text field? Like I said I'm pretty new to all this...


Author Comment

ID: 8099104
Hi again, I will award the points to Cherrypie for the more detailed answer - can you post a quick bit of code to show how to loop through a text field with substring? Thanks

Expert Comment

ID: 8099373
Hi alexessory

You don't need to loop through your string if you use the substring and replace functions

substring will return the full length of your text as varchar and replace will replace all matches it finds

replace( substring(person_message,1,datalength(person_message)), '[Firstname]', Person_Firstname)


Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question