March 10, 2016 at 4:37 am
Hi
I have a table called 'mytable'
we have various records in it such as
Now I want to keep everything before the @ symbol and change everything after the @ symbol to 'uniformEmail.com'
So i would have
I have been using the following code to replace values where there are lots of records with the same email - so i can change a large chunk of records in one go.
UPDATE mytable
SET Email = REPLACE(email,'@theEmailToChange','uniformEmail.com')
So in the above code, I'm just copying the value in that i need to change and it which works fine, however I now have about 100 records with singular unique values and I don't want to keep putting in the value to change for every singular record.
Can I somehow make an update to every record remaining that doesn't have the '@uniformEmail.com' ?
I was trying to use the CHARINDEX function, but I am struggling (although I think I am on the correct path !!!)
March 10, 2016 at 4:52 am
CHARINDEX is in fact the correct tool for the job.
Here is one way to do it:
DECLARE @sampleData TABLE (
email varchar(255)
);
INSERT INTO @sampleData
VALUES
('abc@someemail.com')
,('def@someotheremail.co.uk')
,('xyz@adifferentemail.org');
UPDATE @sampleData
SET email = LEFT(email,CHARINDEX('@',email,1)) + 'uniformEmail.com'
SELECT *
FROM @sampleData
-- Gianluca Sartori
March 10, 2016 at 5:05 am
That is fantastic - that has done exactly what I needed. I was so close with my own effort as well, but it looks like I got the syntax mixed up !!
Thanks very much for your help !!
🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply