This one goes in my “to remember” file.
I created an application to insert records into a database and one of the fields was username. The initial implementation prepended the username with the user’s domain (i.e., “DOMAIN\user”) due to the method used to obtain the username.
After a while I added new functionality to the application that added records in a different way and this time the username was added without the prepended domain name. I wanted to figure out a way to update all the old records with a single query and came up with this:
UPDATE Machines SET UserName = (SUBSTRING(UserName,CHARINDEX(‘\’, UserName) + 1, LEN(UserName) – CHARINDEX(‘\’, UserName) + 1))
WHERE Username LIKE ‘DOMAIN\%’
(Domain name changed to protect the innocent…)
As is usual in cases like this, be careful with update statements. Test well before unleashing on production data.