Parse String Dilema

Question

Parse String Dilema

John524

SSC-Addicted

Points: 473
More actions
December 6, 2019 at 8:33 pm

#3705547

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

ZZartin

SSC-Dedicated

Points: 30920

December 6, 2019 at 9:17 pm

#3705560

Have you looked into trying the reverse function?

John524 SSC-Addicted Points: 473 More actions · Answer 3

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