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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s