Help Parsing String

  • I can do the Last Name, First Name; my problem is Middle Initial.

    I have a field called EmpName, and the Values are:

    EmpName(There is no space after the Comma)

    1.       Smith,John

    2.       Smith,John J

    3.       Smith,John Michael

    4.       Smith Creola,Melisa Lue



    LastName          FirstName                         Middle Initial

    1.       Smith                  John

    2.       Smith                  John                                          J

    3.       Smith                  John Michael

    4.       Smith Creola      Melissa Lue


    And also would like for record #4 to grab “Smith Creo” Only .( I would like to get the complete last Name first part  “Smith” and only the first 4 characters of the second part of the last name “Creola”.


    Thank you for the help in advanced.



  • Correction I need help with also the first name because I'm getting the middle Initial in my first name results;Example First Name Results= John J

  • I suspect you'll find that this is a fairly thankless task, once you expand your dataset to include other name varieties, but this code works for your sample data. It depends on the existence of Jeff Moden's infamous delimitedSplit8K function.


    CREATE TABLE #SomeData
    SomeText1 VARCHAR(200) NOT NULL

    INSERT #SomeData
    ,('Smith,John J')
    ,('Smith,John Michael')
    ,('Smith Creola,Melisa Lue');

    WITH Breakdown1
    AS (SELECT *
    FROM #SomeData sd
    SELECT CommaBreakNo = dsk.ItemNumber
    ,CommaItem = dsk.Item
    FROM dbo.DelimitedSplit8K (sd.SomeText1, ',') dsk
    ) c1
    SELECT SpaceBreakNo = dsk.ItemNumber
    ,SpaceItem = dsk.Item
    FROM dbo.DelimitedSplit8K (c1.CommaItem, ' ') dsk
    ) c2 )
    AS (SELECT TheRest1 = MAX (IIF(bd.CommaBreakNo = 2 AND bd.SpaceBreakNo = 1, bd.SpaceItem, ''))
    ,TheRest2 = MAX (IIF(bd.CommaBreakNo = 2 AND bd.SpaceBreakNo = 2, bd.SpaceItem, ''))
    ,LastName1 = MAX (IIF(bd.CommaBreakNo = 1 AND bd.SpaceBreakNo = 1, bd.SpaceItem, ''))
    ,LastName2 = MAX (IIF(bd.CommaBreakNo = 1 AND bd.SpaceBreakNo = 2, bd.SpaceItem, ''))
    FROM Breakdown1 bd
    GROUP BY bd.SomeText1)
    SELECT LastName = bd2.LastName1 + ' ' + IIF(LEN (bd2.LastName2) > 4, LEFT(bd2.LastName2, 4), bd2.LastName2)
    ,FirstName = bd2.TheRest1 + IIF(c3.IsMiddle = 1, '', CONCAT (' ', bd2.TheRest2))
    ,MiddleInitial = IIF(c3.IsMiddle = 1, bd2.TheRest2, '')
    FROM Breakdown2 bd2
    (SELECT IsMiddle = IIF(LEN (bd2.TheRest2) = 1, 1, 0)) c3;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Yes, I searched and came across "Jeff Moden's infamous delimitedSplit8K function". I was looking for an alternative way.  I will implement Jeff Moden's infamous delimitedSplit8K function.  Thanks for the reply.

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

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