?? on usiong a replace on an update

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  November 5, 2013 at 10:40 am

  #279727

  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

• a20213

  Say Hey Kid

  Points: 664

  More actions

  November 5, 2013 at 10:53 am

  #1664203

  can you try ?

  update table set field=REPLACE(field, '@', '\')...

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  November 5, 2013 at 12:10 pm

  #1664218

  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

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  November 5, 2013 at 2:29 pm

  #1664262

  Thanks Jeff,

  Thant works great.

  Now I just want to dissect it so I totally understand it 🙂

  Thanks Again