Find, Extract and Replace Data in a Field
Posted on 2006-05-25
Okay, here's a stumper. I know there are you experts out there who know a lot more about T-SQL than I will ever know. But then, I was an electrician for 14 years before becoming a programmer, so at least I can wire up your house.
I am doing a system conversion. The current employee table has, like most, a first and last name. I am adding a column to the new table called CommonName. So if your name is William Smith, but you go by Bill, the FirstName column would contain William and the CommonName field would contain Bill.
Here is where it gets tricky. In the current system, if a person uses a common name, HR will enter 'William (Bill)' or 'William "Bill"'. Do you see where I am going with this yet?
I want to find either a " or a '(' in the field, extract the information from that point to the 'closing token - either a " or ')', strip out the 'tokens' and put the result in the CommonName field, and update the FirstName field with the data up to the 'token'. In other words, I would replace 'William (Bill)' with 'William' and put Bill in the CommonName field. I'll bet it can be done in T-SQL, I just don't know how.