SQL Replace() function on a select or variable

Question

insert into PendingEmails (Subject,Body,ToEmail,PendingEmailStatusID)
select REPLACE( select subject from dbo.EmailTemplates where ID  = 7 ), '[CallID]'), (select Body from dbo.EmailTemplates where ID  = 7) ,contact.Email  ,5 
FROM  inserted AS i inner join Calls on i.CallID = Calls.CallId inner join Contact on calls.ContactID = Contact.ContactID
where contact.Email is not null and  contact.Email <> ''

I want to replace '[CallID]' in the subject with a value. that syntax is not working. I also tried using a variable, wont work either.

What is the best way to do that?

RichardTheKiwi · Accepted Answer · 2011-04-14 21:15:59Z

3

insert into PendingEmails (Subject)
select REPLACE(subject, '[CallID]', @callid)
from dbo.EmailTemplates
where ID  = 7

I can't see where you are joining to the call table to get calls.callid, have you already stored it into a variable?

You can skip the variable if you have a subquery that will produce it (bracket the scalar subquery)

insert into PendingEmails (Subject)
select REPLACE(subject, '[CallID]', (select callid from calls where id=9))
from dbo.EmailTemplates
where ID = 7

Or JOIN it

insert into PendingEmails (Subject)
select REPLACE(e.subject, '[CallID]', c.callid)
from dbo.EmailTemplates e
join calls c on c.id=9
where e.ID = 7

answered Apr 14, 2011 at 21:15

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pete M · Accepted Answer · 2011-04-14 21:14:11Z

1

You have some parenthesis shenanigans:

insert into PendingEmails (Subject) 
select REPLACE(( select subject from dbo.EmailTemplates where ID = 7 ), '[CallID]' , calls.callid)

answered Apr 14, 2011 at 21:14

Pete M

2,04811 silver badges17 bronze badges

2 Comments

Pete M Over a year ago

Thanks for the accept. However, I think Richard has the better answer. I'd go with that instead.

Ezi Over a year ago

your right.. i first tried this and it works so I hit acceded, Now I tried the other one it's nicer... Thanks a lot.

Chandu · Accepted Answer · 2011-04-14 21:15:36Z

1

INSERT INTO PendingEmails (Subject) 
SELECT REPLACE(subject, '[CallID]' , calls.callid)
    FROM dbo.EmailTemplates 
 WHERE ID = 7

answered Apr 14, 2011 at 21:15

Chandu

83.2k19 gold badges137 silver badges136 bronze badges

1 Comment

RichardTheKiwi Over a year ago

where does calls.callid come from?

Collectives™ on Stack Overflow

SQL Replace() function on a select or variable

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related