REPLACE function in stored procedure to update text field?

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

Who is Participating?
cherrypieConnect With a Mentor Commented:
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)

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.
alexessoryAuthor Commented:
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...

alexessoryAuthor Commented:
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
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)

All Courses

From novice to tech pro — start learning today.