Find, Extract and Replace Data in a Field

Posted on 2006-05-25
Last Modified: 2008-03-06
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.
Question by:dbbishop
    LVL 34

    Expert Comment

    by:Brian Crowe
    SELECT RTRIM(SUBSTRING(@text, 1, CHARINDEX(@delim, @text) - 1)) AS FirstName,
          SUBSTRING(@text, CHARINDEX(@delim, @text) + 1, LEN(@text) - CHARINDEX(@delim, @text) - 1) AS CommonName
    LVL 34

    Expert Comment

    by:Brian Crowe
    oops...little premature on that one

    DECLARE @delim char(1)
    SET @delim = '"'

    UPDATE myTable
    SET FirstName = RTRIM(SUBSTRING(FirstName, 1, CHARINDEX(@delim, FirstName) - 1)),
       CommonName = SUBSTRING(FirstName, CHARINDEX(@delim, FirstName) + 1, LEN(FirstName) - CHARINDEX(@delim, FirstName) - 1)

    run it twice with each delimiter '(' and '"'
    LVL 34

    Accepted Solution

    that should have read...

    run it once with each delimiter '(' and '"'
    LVL 15

    Author Comment


    It works when there is a delimiter, but if there isn't, I get an error (Invalid length parameter passed to the substring function.)

    I made the following change and it works.

    UPDATE tblEmployees
    SET FirstName = CASE WHEN CHARINDEX(@delim, FirstName) = 0 THEN FirstName
            ELSE RTRIM(SUBSTRING(FirstName, 1, CHARINDEX(@delim, FirstName) - 1)) END,
    CASE WHEN CHARINDEX(@delim, FirstName) = 0 THEN ''
            ELSE SUBSTRING(FirstName, CHARINDEX(@delim, FirstName) + 1, LEN(FirstName) - CHARINDEX(@delim, FirstName) - 1) END

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now