Home Using T-SQL REPLACE with wildcard lookup to replace field values
Reply: 1

Using T-SQL REPLACE with wildcard lookup to replace field values

Mark
1#
Mark Published in 2017-11-13 21:51:39Z

I have table with schema:

CREATE TABLE InvestorDetails
(
    [ID] INT PRIMARY KEY,
    [Created] DATETIME,
    [Updated] DATETIME,
    [ToEmail] VARCHAR(MAX)
)

Records in InvestorDetails look like the following:

1, 2017-01-01, 2017-02-02, ames@gmail.com;mark@gmail.com;tom@gmail.com;mitchel@gmail.com
2, 2017,01-02, 2017-02-02, ben@gmail.com;michael@gmail.com;mark@gmail.com
3, 2017-02-02, 2017,02-03, test@gmail.com;matthew@gmail.com;dan@gmail.com;jay@gmail.com;mark@gmail.com

I want to write a query that will go through and update each record in the table that has 'mark@gmail.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, ames@gmail.com;markTest@gmail.com;tom@gmail.com;mitchel@gmail.com

I can find the records with mark@gmail.com in 'EmailRecipients'using the following query:

SELECT I.ID, I.EmailRecipients
FROM InvestorDetails I
WHERE I.EmailRecipients LIKE '%mark@gmail.com%' 

The tricky part is ensuring only the mark@gmail.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!

Thanks!

Martin Smith
2#
Martin Smith Reply to 2017-11-14 06:05:59Z

You can use the following query

UPDATE InvestorDetails
SET EmailRecipients = 
       REPLACE(';' + EmailRecipients  + ';', ';mark@gmail.com;' , ';markTest@gmail.com;' )
WHERE EmailRecipients LIKE '%;mark@gmail.com;%'

This is made to ensure that mails that contains mark such as drmark@gmail.com are not updated.

If you want to remove the added leadin and trailing semicolons you can use the following query

You can use the following query

UPDATE InvestorDetails
SET EmailRecipients = 
       SUBSTRING(REPLACE(';' + EmailRecipients  + ';', ';mark@gmail.com;' , ';markTest@gmail.com;'),2, LEN( REPLACE(';' + EmailRecipients  + ';', ';mark@gmail.com;' , ';markTest@gmail.com;' ) - 2 )
WHERE EmailRecipients LIKE '%;mark@gmail.com;%' AND ISNULL(EmailRecipients,'') <> ''

Another query was provided by @MartinSmith in the comments

UPDATE InvestorDetails 
SET ToEmail = SUBSTRING(replaced, 2, LEN(replaced) -2) 
FROM InvestorDetails
    CROSS APPLY
(SELECT REPLACE(';'+ ToEmail + ';',';mark@gmail.com;' , ';markTest@gmail.com;')) CA(replaced) 
WHERE ToEmail LIKE '%mark@gmail.com%'
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.326961 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO