Update query? Or other solution?

Question

I have an Email column with invalid addresses in them e.g. “[email protected],[email protected]”. (It includes the quotes)

My select statement cleans these up:

SELECT SUBSTRING([Email], 2, PATINDEX(‘%,%’, [Email] – 2))
FROM table
WHERE [Email] LIKE ‘”%”’

This works and displays: [email protected]

Problem I have is, I have a multitude of records with the incorrect format and I need to update them with the result from the SELECT query above. Any ideas?

Answer 1

UPDATE table SET 
    [Email] = SUBSTRING([Email], 2, PATINDEX(‘%,%’, [Email] – 2)) 
WHERE 
    [Email] LIKE ‘”%”’

Answer 2

UPDATE table
SET Email = SUBSTRING([Email], 2, PATINDEX(‘%,%’, [Email] – 2))
WHERE [Email] LIKE ‘”%”’

Should do the trick. Of course, test it out on test data first :)

Answer 3

You can use an UPDATE statement like this:

update table 
set Email = SUBSTRING(Email, 2, PATINDEX('%,%', Email – 2)) 
WHERE Email LIKE '"%"'

Answer 4

UPDATE table
SET [Email] = SUBSTRING([Email], 2, PATINDEX(‘%,%’, [Email] – 2))
WHERE [Email] LIKE ‘”%”’

Just make sure beforehand that this like expression is only matching these problematic email addresses, and you're not picking up other rows with different values.

Answer 5

to be sure you don't lose any valid e-mails, just save the changed ones:

CREATE TABLE ChangedEmails
(
PK ...
Email ...
)

then fix the e-mails and save the changed ones (in one statement)

UPDATE YourTable
    SET Email=SUBSTRING([Email], 2, PATINDEX(‘%,%’, [Email] – 2)) 
    OUTPUT INSERTED.PK,DELETED.Email
        INTO ChangedEmails
WHERE 
    Email LIKE ‘”%”’

now if there is any doubt, you can see what the previous email was before you changed them.

ALSO, based on the OPs comment:

This is what I came up with but I get a PK violation key, so I guess I need to find which rows are causing this. Thanks!

DON'T make the PK an e-mail address!! use an identity as the PK and make the e-mail just a data column. Use this to "fix" your table:

ALTER TABLE YourTable ADD YourPKID int NOT NULL IDENTITY (1, 1)
GO

ALTER TABLE YourTable DROP CONSTRAINT PK_YourTable
GO

ALTER TABLE dbo.JobApps ADD CONSTRAINT
    PK_YourTable PRIMARY KEY CLUSTERED 
    (
    YourPKID 
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO