Parse String Dilema

  • Hello,

    Try and try as I may, I'm struggling with this one.  As the topic suggests, I have a name field which varies greatly.  Basically, it's in the LastName, [space] FirstName format.  However, many have middle initials, hyphenated last names, some are Juniors, some are III's you name it.

    Examples.... Doe, Jane   Doe, Jane E     Doe III, Jane     Doe Smith, Jane E

    I'm sure you get the gist  All these need to be FirstName=Jane  LastName = Doe....Long story short, I simply need to extract the Last Name and the First Name.  Fortunately, there is some consistency in that the LastName is everything leftmost in the string up to the first space not including the comma, and FirstName is everything after the comma [space] combo, but before the next [space]   I have figured out the LastName piece (using CHARINDEX and SUBSTRING), but getting the First Name out of this has really been a struggle.  Any help would be terrific

    thank you so much

  • Here is a grossly inelegant solution that I knocked up quickly. Others will no doubt come up with something nicer to look at!

    DROP TABLE IF EXISTS #Names;

    CREATE TABLE #Names
    (
    FullName VARCHAR(50) NOT NULL
    );

    INSERT #Names
    (
    FullName
    )
    VALUES
    ('Doe, Jane')
    ,('Doe, Jane')
    ,('Doe, Jane E')
    ,('Doe III, Jane');

    SELECT n.FullName
    ,LastName = REPLACE(LEFT(n.FullName, p.SpacePos - 1), ',', '')
    ,FirstName = TRIM(SUBSTRING(n.FullName, p.CommaPos + 1, p2.SpacePos2 - p.CommaPos))
    FROM #Names n
    CROSS APPLY
    (
    SELECT SpacePos = CHARINDEX(' ', n.FullName)
    ,CommaPos = CHARINDEX(',', n.FullName)
    ) p
    CROSS APPLY
    (
    SELECT SpacePos2 = IIF(CHARINDEX(' ', n.FullName, p.CommaPos + 2) = 0
    ,LEN(n.FullName)
    ,CHARINDEX(' ', n.FullName, p.CommaPos + 2))
    ) p2;

    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.

  • Have you looked into trying the reverse function?

  • thanks Phil!

    I'm gonna look into this.  Looks like this will fit the bill.

    ~wish I could come up with this stuff off the top of my head

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

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