Category Archives: To Remember

Removing a common substring from multiple records

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.