I have table with schema:
CREATE TABLE InvestorDetails
[ID] INT PRIMARY KEY,
Records in InvestorDetails look like the following:
1, 2017-01-01, 2017-02-02, email@example.com;firstname.lastname@example.org;email@example.com;firstname.lastname@example.org
2, 2017,01-02, 2017-02-02, email@example.com;firstname.lastname@example.org;email@example.com
3, 2017-02-02, 2017,02-03, firstname.lastname@example.org;email@example.com;firstname.lastname@example.org;email@example.com;firstname.lastname@example.org
I want to write a query that will go through and update each record in the table that has 'email@example.com' in field 'ToEmail' with 'markTest@gmail.com'
So for example in record 1 above, I want it to look like:
1, 2017-01-01, 2017-02-02, firstname.lastname@example.org;markTest@gmail.com;email@example.com;firstname.lastname@example.org
I can find the records with email@example.com in 'EmailRecipients'using the following query:
SELECT I.ID, I.EmailRecipients
FROM InvestorDetails I
WHERE I.EmailRecipients LIKE '%firstname.lastname@example.org%'
The tricky part is ensuring only the email@example.com is changed, and the rest remains. I believe this can be achieved with TSQL REPLACE, but I'm having trouble writing the SQL, particularly what to use as the string_pattern and string_replacement parameters.
Any help is greatly appreciated!