November 5, 2013 at 10:40 am
Hi
I'm looking to update a field
It currently has username@domain I want to update to domain\username
for example jsmith@company should be company\jsmith
Thanks In Advance
Joe
November 5, 2013 at 10:53 am
can you try ?
update table set field=REPLACE(field, '@', '\')...
November 5, 2013 at 12:10 pm
a20213 (11/5/2013)
can you try ?
update table set field=REPLACE(field, '@', '\')...
Won't work - as the OP is looking to change it from username@domain to domain\username.
You can do something like this:
UPDATE yourtable
SET column = replace(column, 'jsmith@domain', 'domain\jsmith')
WHERE column = 'jsmith@domain';
Problem here is that you have to build it out for every one you want updated. If you want to do everything at once then you need to parse the column to get the username and domain.
This will involve using SUBSTRING and CHARINDEX to pull out the values - combining them into the new formatted name.
UPDATE yourtable
SET column = SUBSTRING(column, CHARINDEX('@', column, 1) + 1, 99) + '\' + SUBSTRING(column, 0, charindex('@', column, 1))
You can test to make sure everything is working by changing the update to a SELECT and validating the before/after values.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 5, 2013 at 2:29 pm
Thanks Jeff,
Thant works great.
Now I just want to dissect it so I totally understand it 🙂
Thanks Again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply