Find First, Middle and Last Name of the Second Consumer

• Loner

  SSC-Insane

  Points: 21279

  More actions

  January 20, 2017 at 12:34 pm

  #325772

  I have a Name column that may or may not have Second Consumer and if it does, it may or may not have Middle name.
  I need to extract the second consumer First Name, Middle Name (if exists) and Last Name.  The first and second consumer is connected by 'AND'

  CREATE TABLE #Name (Name VARCHAR(250),
                                        NameCTR VARCHAR(4))

  INSERT INTO #Name (Name)
  SELECT 'ANDREW JONES', 'JONE'
  UNION
  SELECT 'BILL A AND MARY B SMITH', 'SMIT'
  UNION
  SELECT 'TOM AND MARIA BAND', 'BAND'
  UNION
  SELECT 'STEVEN AND JESSICA E CODE', 'CODE'
  UNION
  SELECT 'VINCE AND MOLLY ALICE MATT', 'MATT'

  NameCTR is the first 4 characters of the Last Name.

  MARY B SMITH
  MARIA BAND
  JESSICA E CODE
  MOLLY ALICE MATT

  I have a hard to get the middle name.

  Thanks

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 20, 2017 at 12:58 pm

  #1924139

  Something like this?
  SELECT LEFT(N.Name, PATINDEX('% AND %', N.Name)-1) + RIGHT(N.Name, CHARINDEX(' ',REVERSE(N.Name))) AS FirstConsumer,
       RIGHT(N.Name, LEN(N.Name) - (PATINDEX('% AND %', N.Name) + 4)) AS SecondConsumer
FROM #NAME N
WHERE N.Name LIKE '% AND %';

  P.s. "I have a hard to get the middle name." is NOT the best way to say that in English 😉

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Phil Parkin

  SSC Guru

  Points: 246970

  More actions

  January 20, 2017 at 1:08 pm

  #1924145

  Thom A - Friday, January 20, 2017 12:58 PM

  P.s. "I have a hard to get the middle name." is NOT the best way to say that in English 😉

  That's a statement from someone who really loves their coding.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Loner

  SSC-Insane

  Points: 21279

  More actions

  January 20, 2017 at 1:23 pm

  #1924148

  Sorry I mean I have a hard time to get the middle name.
  Thanks

• Loner

  SSC-Insane

  Points: 21279

  More actions

  January 20, 2017 at 1:26 pm

  #1924149

  BTW I want the name in different columns.

  First name - Mary
  Middle Name - B
  Last Name - SMITHThanks

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 20, 2017 at 1:47 pm

  #1924150

  Maybe something like this:
  
SELECT LEFT( SecondName, CHARINDEX( ' ', SecondName) - 1) AS FirstName,
   SUBSTRING( SecondName, CHARINDEX( ' ', SecondName) + 1, 800) AS MiddleName,
   SUBSTRING( Name, LastNameStart, 800) AS LastName,
   n.*
FROM #Name n
CROSS APPLY (SELECT CHARINDEX(' AND ', Name)+5 AS SecondStart, CHARINDEX(NameCTR, Name) AS LastNameStart)x
OUTER APPLY (SELECT SUBSTRING(Name, SecondStart, LastNameStart - SecondStart) SecondName
    WHERE SecondStart > 5
    AND LastNameStart > SecondStart)y;

  I would strongly suggest that you correct this design to comply with the first normal form. That would make your queries a lot simpler and more efficient.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 20, 2017 at 1:56 pm

  #1924151

  Loner - Friday, January 20, 2017 1:26 PM

  BTW I want the name in different columns.

  First name - Mary
  Middle Name - B
  Last Name - SMITHThanks

  Well, that changes the game.

  My solution:
  WITH SC AS(
  SELECT RIGHT(N.Name, LEN(N.Name) - (PATINDEX('% AND %', N.Name) + 4)) AS SecondConsumer
  FROM #NAME N
  WHERE N.Name LIKE '% AND %'),
SCS AS (
  SELECT LEFT(SC.SecondConsumer, CHARINDEX(' ',SC.SecondConsumer) - 1) AS FirstName,
    RIGHT(SC.SecondConsumer, CHARINDEX(' ',REVERSE(SC.SecondConsumer))-1) AS Surname,
    *
  FROM SC)
SELECT FirstName,
   CASE WHEN LEN(SCS.Firstname) + LEN(SCS.Surname) + 1 < LEN(SCS.SecondConsumer)
    THEN SUBSTRING(SCS.SecondConsumer, LEN(SCS.Firstname) +2 , LEN(SCS.SecondConsumer) - (LEN(SCS.Firstname) + LEN(SCS.Surname) + 1) - 1) END AS MiddleName,
   SCS.Surname,
   SecondConsumer
FROM SCS;

  @luis, I would not be surprised if this is Vendor based.

  EDIT: Slight amendment due to leading whitespace on middlename.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk