complicated SQL help

Perhaps you can help with something that I consider kind of omplicated? (I am SO hoping that I am NOT going to have to do this manually!!!)

I have a table (about 3000 rows) where two of the columns have Domain User information.

COL1 has DOMAIN\Username and COL2 has (or SHOULD have) DOMAIN@.username.com

I need to look at each field in COL1 and if exists DOMAIN\username, I need to populate COL2 with username@.DOMAIN.com

Is this possible?very possibleupdate daTable
set COL2 = substring(COL1
,charindex('\',COL1)+1
,LEN(COL1)-charindex('\',COL1)
)
+ '@.' +
left(COL1,charindex('\',COL1)-1)
where coalesce(COL1,'') > ''tip: back up your data before trying this|||Thank you! That's a start! I learn so much from these forums!!

Problem is... I still do not have the .com part of the username@.domain.com in COL2

I also found out that once I populate COL2 correctly, I need to CLEAR any field in COL2 that does not adhere to "username@.domain.com" and then change domain to correctdomain so that all of the poplated fields in COL2 will read username@.correctdomain.com|||Problem is... I still do not have the .com part of the username@.domain.com in COL2well, that's easy, just concatenate '.com' onto the end! ;)|||Thanks.. what about the fields that got pulled over that don't adhere to the username@.Domain.com criteria? Is there a way to clear any fields that are not LIKE username@.Domain.com ?|||What do you have beside DOMAIN\username in the column?

select COl1, COL2
from daTable
where COL1 not like 'DOMAIN%'|||My COL2 is now correct. all fields have username@.DOMAIN.com

What I need to do is clear or delete any fields that do not have specially
username@.domain.com

in other words. I need to keep all fields in COL2 that are LIKE @.DOMAIN.com
and delete all others (some are username@.anotherdomain.com or username@.yetanotherdomain.com)

I tried
Delete from MYTABLE
Where COL2 LIKE '%@.anotherdomain.com%'

but that deleted the whole row.. I need to just clear the field in COL2|||Try this:

UPDATE myTable
SET COL2 = NULL
WHERE COL2 NOT LIKE '%@.theDomainRecordsToKeep.com'|||THANK YOU!!!

This is the code I ended up using..

UPDATE MYTABLE
SET COL2= PARSENAME(REPLACE(COL1, '\', '.'), 1) + '@.' +
PARSENAME(REPLACE(COL1, '\', '.'), 2) + '.com'
WHERE COL1 LIKE '%DOMAIN\%'
go
UPDATE MYTABLE
SET COL2 = Replace(COL2,'DOMAIN','CORRECTDOMAIN')
go

Seems to work..