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

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  December 6, 2019 at 9:16 pm

  #3705559

  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;

  ---

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  December 6, 2019 at 9:17 pm

  #3705560

  Have you looked into trying the reverse function?

• John524

  SSC-Addicted

  Points: 473

  More actions

  December 6, 2019 at 9:24 pm

  #3705561

  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