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, firstname.lastname@example.org;email@example.com;firstname.lastname@example.org;email@example.com
2, 2017,01-02, 2017-02-02, firstname.lastname@example.org;email@example.com;firstname.lastname@example.org
3, 2017-02-02, 2017,02-03, email@example.com;firstname.lastname@example.org;email@example.com;firstname.lastname@example.org;email@example.com
I want to write a query that will go through and update each record in the table that has 'firstname.lastname@example.org' 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, email@example.com;markTest@gmail.com;firstname.lastname@example.org;email@example.com
I can find the records with firstname.lastname@example.org in 'EmailRecipients'using the following query:
SELECT I.ID, I.EmailRecipients
FROM InvestorDetails I
WHERE I.EmailRecipients LIKE '%email@example.com%'
The tricky part is ensuring only the firstname.lastname@example.org 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!