Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
 Posted - 2007-04-27 : 09:08:07
|
| 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.comI need to look at each field in COL1 and if exists DOMAIN\username, I need to populate COL2 with username@DOMAIN.comIs this possible??? |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-04-27 : 09:38:06
|
| Shouldn't it be username@DOMAIN.com and not DOMAIN@username.com. Assuming that it is username@DOMAIN.com, then you can try the following:UPDATE YourTableSET COL2 = PARSENAME(REPLACE(COL1, '\', '.'), 1) + '@' + PARSENAME(REPLACE(COL1, '\', '.'), 2) + '.com'WHERE COL1 LIKE '%\%'SQL Server Helperhttp://www.sql-server-helper.com |
 |
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2007-04-27 : 09:56:52
|
| Awesome!! I learn so much in these forums !!! That worked great!what I need to do now is a remove and replace in COL2. I need to clear any field in COL2 that doest not adhere to username@DOMAIN.com and then change any occurance of DOMAIN to CORRECTDOMAIN |
|
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2007-04-27 : 10:36:58
|
| I was able to change DOMAIN to CORRECT domain with a simple replace like this: UPDATE [tablename] SET [columnname] = Replace([columnname],'existing string value','new string value')but I still need to clear any fields in COL2 that do not adhere to the useranme@Domain.com is there a way to do that???? |
|
|
|
|
|
|
update? Select? replace?