I've a table called EmailTemplates, which has the column MessageText. For each row, I'd like to add a Please review details before Thank you. For some rows, there might text after thank you.
BEFORE
All the correspondence will be sent to your address.
Thank you!
AFTER
All the correspondence will be sent to your address.
Please review details
Thank you!
I don't know whether REPLACE is the right function for that.
It really depends on whether you want to replace ALL instances of 'Thank you' with 'Please review details Thank you' or just the last one. The implementation below, whilst awfully hacky, shows you how it is possible to do both (although if this is for production use I'd strongly recommend refactoring the code to make it less messy).
declare @myTestTable as table
(
id int identity(1,1) not null primary key clustered,
textValue varchar(500) not null
);
insert into @myTestTable(textValue)
values
('All correspondence will be sent to your addres. Thank you!'),
('I will get back to you. Thank you for your patience'),
('Thank you for calling us up'),
('You didn''t have to do what you did, but I thank you for it, so thank you!');
select m.*, REPLACE(m.textValue, 'thank you', 'Please review details. Thank you') as blindReplace,
SUBSTRING(m.textValue, 0, len(m.textValue) - CHARINDEX(reverse('thank you'), reverse(m.textValue)) + 2 - len('thank you'))
+ 'Please review details ' +
SUBSTRING(m.textValue, len(m.textValue) - CHARINDEX(reverse('thank you'), reverse(m.textValue)) + 2 -len('thank you'), len(m.textValue)) as lastReplace
from @myTestTable as m
Try this:
UPDATE EmailTemplates
SET MessageText = LEFT(MessageText, LEN(MessageText) - 10) + 'Please review details.
Thank You!'
LEN? Is there a way just to replace Thank you? In fact, depending on the row, there might be some text after Thank you!.
before the last sentence, which is Thank you.. The keyword is last.REPLACE can be used in your situation, and an example of how to use it was in the now deleted answer by @juergen d: Here it is again (slightly changed to better match):
UPDATE EmailTemplates
SET MessageText = REPLACE(ColumnName, 'Thank you!', 'Please review details.
Thank you!')
WHERE MessageText LIKE '%Thank you!%';
You can use a multiline replacement string like above or you can encode EOLs with CHAR(13)+CHAR(10):
REPLACE(
ColumnName,
'Thank you!',
'Please review details.' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + 'Thank you!'
)
There is also method which uses STUFF and CHARINDEX:
UPDATE EmailTemplates
SET MessageText = STUFF(
ColumnName,
CHARINDEX('Thank you!', ColumnName),
0,
'Please review details.
'
)
WHERE MessageText LIKE '%Thank you!%';
This method is certainly more verbose. Technically, it is not strictly equivalent to REPLACE, because REPLACE processes all occurrences of the search term and STUFF applies to just one specific position/fragment of the string. But if Thank you! cannot occur more than once in a template, the two methods can be considered equivalent.
