SQL - select and delete query - clean data not matching criteria

[jhowardcarter]

Hi,

Within a table (dbo.accounts) of over 190k records, I have an email address field (varchar(50), null), within which is all sorts of data (most not email addresses). I'm looking for a method the clear the data within the field which is not an email address, and return this to a NULL value.

It's not clean, but I'm happy to trust a text string containing an "@" sign is an email, and delete everything else.

Can this be acheived within a select/update query?

Thanks, in advance.

 

[gmmastros]

This is possible. In the code I show below, I create a table variable and dummy up some data. This allows you to 'try' different things without affecting your data. If you are satisfied this works, then update your table using the same method described below.

Declare @Temp Table(Email VarChar(50))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('')
Insert Into @Temp Values('Valid@Email.com')
Insert Into @Temp Values('NoWay.com')

Select * From @Temp

Update @Temp
Set		email = NULL
Where   Email Not Like '%@%'

Select * From @Temp

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jhowardcarter]

spot on, thanks.

enjoy your day!