Post reply

Substring Query to pull firstname and lastname out of one column

  • December 19, 2012 at 8:28 am

    #271832

    I Have a table with a field called name that is formated as the examples below. I need to pull the firstname as firstname and the lastname as lastname and the DOB as DOB. DOb is the first date in the field.

    I am having a hard time getting it to pull between the -.

    Basic format: these will all have the same number of '-' but there could be an extra initial in the name as the sample below shows for A. Marion Groseth. how can I get it to get past those names with the extra initial as well?

    HIST-AS-JEFFERY WINKEL-02/26/1976-07/06/2012

    HIST-AS-JOSEPH WALDNER-07/07/1965-07/06/2012

    HIST-AS-TERESA KLINE-10/09/1965-02/13/2012

    HIST-HS-A. MARION GROSETH-11/10/1931-01/19/2009

    HIST-HS-AARON ALFRED-07/06/1967-06/02/2010

  • December 19, 2012 at 8:39 am

    #1570126

    well, one way is to use the delimitedSplit8K function to chop the string into bitesize peices.

    from there, you might need to cross apply, modifying the WHERE statement to limit it to teh data that's mostly correct.

    take alook at this example...you'll see how the name is chopped into 2 or 3 pieces per ID, based on your sample data.

    With mySampleData(Id,String)

    AS

    (

    SELECT 1,'HIST-AS-JEFFERY WINKEL-02/26/1976-07/06/2012' UNION ALL

    SELECT 2,'HIST-AS-JOSEPH WALDNER-07/07/1965-07/06/2012' UNION ALL

    SELECT 3,'HIST-AS-TERESA KLINE-10/09/1965-02/13/2012' UNION ALL

    SELECT 4,'HIST-HS-A. MARION GROSETH-11/10/1931-01/19/2009' UNION ALL

    SELECT 5,'HIST-HS-AARON ALFRED-07/06/1967-06/02/2010'

    )

    select

    mySampleData.*,

    ByDashes.*,

    BySpaces.*

    from mySampleData

    CROSS APPLY dbo.DelimitedSplit8K(mySampleData.String,'-') ByDashes

    CROSS APPLY dbo.DelimitedSplit8K(ByDashes.Item,' ') BySpaces

    WHERE ByDashes.ItemNumber = 3 --the 3rd dash group has the values we want to further split by spaces

    {edit}

    here's the article containing the DelimitedSplit8K funcrtion:

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 19, 2012 at 9:39 am

    #1570173

    That works great. I had never seen that function scipt before. I just had to then create a script to catch the firstname , lastname and initial based on the ID and item numbers into the columns I needed.

    Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply