CODEWITHSUNDEEP
sqlsql-serverselectreplace
Ezi
Ezi

Reputation: 2210

SQL Replace() function on a select or variable

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?

Upvotes: 0

Views: 16142

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

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

Upvotes: 3

Chandu
Chandu

Reputation: 82943

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

Upvotes: 1

Pete M
Pete M

Reputation: 2048

You have some parenthesis shenanigans:

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

Upvotes: 1

Related Questions