SQL parse the first middle and last name from a fullname field

Question

How do I parse the first  middle  and last name out of a fullname field with SQL     I need to try to match up on names that are not a direct match on full name  I d like to be able to take the full name field and break it up into first  middle and last name    The data does not include any prefixes or suffixes  The middle name is optional  The data is formatted  First Middle Last    I m interested in some practical solutions to get me 90  of the way there   As it has been stated  this is a complex problem  so I ll handle special cases individually

User · Answer

It s difficult to answer without knowing how the  full name  is formatted   It could be  Last Name  First Name Middle Name  or  First Name Middle Name Last Name   etc   Basically you ll have to use the SUBSTRING function  SUBSTRING   expression   start   length     And probably the CHARINDEX function   CHARINDEX  substr  expression    To figure out the start and length for each part you want to extract   So let s say the format is  First Name  Last Name  you could  untested   but should be close      SELECT  SUBSTRING fullname  1  CHARINDEX      fullname  - 1  AS FirstName   SUBSTRING fullname  CHARINDEX      fullname    1  len fullname   AS LastName FROM YourTable

User · Answer

If you are trying to parse apart a human name in PHP  I recommend Keith Beckman s nameparse php script   Copy in case site goes down     lt      Name    nameparse php Version  0 2a Date    030507 First   030407 License     GNU General Public License v2 Bugs    If one of the words in the middle name is Ben  or St   for that matter           or any other possible last-name prefix  the name MUST be entered in         last-name-first format  If the last-name parsing routines get ahold         of any prefix  they tie up the rest of the name up to the suffix  i e            William Ben Carey   would yield  Ben Carey  as the last name  while          Carey  William Ben  would yield  Carey  as last and  Ben  as middle           This is a problem inherent in the prefix-parsing routines algorithm          and probably will not be fixed  It s not my fault that there s some         odd overlap between various languages  Just don t name your kids          Something Ben Something   and you should be alright       function    norm str  string        return  trim strtolower          str replace         string            function    in array norm  needle  haystack        return  in array norm str  needle   haystack          function    parse name  fullname         titles             array  dr   miss   mr   mrs   ms   judge         prefices           array  ben   bin   da   dal   de   del   der   de   e                                la   le   san   st   ste   van   vel   von         suffices           array  esq   esquire   jr   sr   2   ii   iii   iv          pieces             explode     preg replace    s        trim  fullname          n pieces           count  pieces        switch  n pieces            case    1      array title first middles last suffix               subp       explode     trim  pieces 0                  n subp     count  subp               for  i   0   i  lt   n subp   i                       curr                   trim  subp  i                     next                   trim  subp  i 1                     if  i    0  amp  amp  in array norm  curr  titles                          out  title          curr                      continue                                         if   out  first                           out  first          curr                      continue                                         if  i     n subp-2  amp  amp   next  amp  amp  in array norm  next  suffices                         if  out  last                               out  last             curr                                                 else                            out  last           curr                                                 out  suffix             next                      break                                         if  i     n subp-1                        if  out  last                               out  last             curr                                                 else                            out  last           curr                                                continue                                         if in array norm  curr  prefices                         if  out  last                               out  last             curr                                                 else                            out  last           curr                                                continue                                         if  next     y      next     Y                         if  out  last                               out  last             curr                                                 else                            out  last           curr                                                continue                                         if  out  last                           out  last             curr                       continue                                         if  out  middle                           out  middle               curr                                         else                        out  middle             curr                                                      break          case    2                  switch in array norm  pieces 1   suffices                         case    TRUE     array title first middles last suffix                           subp       explode     trim  pieces 0                              n subp     count  subp                           for  i   0   i  lt   n subp   i                                   curr                   trim  subp  i                                 next                   trim  subp  i 1                                 if  i    0  amp  amp  in array norm  curr  titles                                      out  title          curr                                  continue                                                                 if   out  first                                       out  first          curr                                  continue                                                                 if  i     n subp-1                                    if  out  last                                           out  last             curr                                                                         else                                        out  last           curr                                                                        continue                                                                 if in array norm  curr  prefices                                     if  out  last                                           out  last             curr                                                                         else                                        out  last           curr                                                                        continue                                                                 if  next     y      next     Y                                     if  out  last                                           out  last             curr                                                                         else                                        out  last           curr                                                                        continue                                                                 if  out  last                                       out  last             curr                                   continue                                                                 if  out  middle                                       out  middle               curr                                                                 else                                    out  middle             curr                                                                                                                  out  suffix        trim  pieces 1                            break                      case    FALSE     array last title first middles suffix                           subp       explode     trim  pieces 1                              n subp     count  subp                           for  i   0   i  lt   n subp   i                                   curr                   trim  subp  i                                 next                   trim  subp  i 1                                 if  i    0  amp  amp  in array norm  curr  titles                                      out  title          curr                                  continue                                                                 if   out  first                                       out  first          curr                                  continue                                                             if  i     n subp-2  amp  amp   next  amp  amp                              in array norm  next  suffices                                 if  out  middle                                       out  middle           curr                                                                 else                                    out  middle         curr                                                                 out  suffix             next                              break                                                         if  i     n subp-1  amp  amp  in array norm  curr  suffices                                  out  suffix             curr                              continue                                                         if  out  middle                                   out  middle               curr                                                         else                                out  middle             curr                                                                                   out  last           pieces 0                           break                                    unset  pieces               break          case    3      array last title first middles suffix               subp       explode     trim  pieces 1                  n subp     count  subp               for  i   0   i  lt   n subp   i                       curr                   trim  subp  i                     next                   trim  subp  i 1                    if  i    0  amp  amp  in array norm  curr  titles                          out  title          curr                      continue                                         if   out  first                           out  first          curr                      continue                                         if  out  middle                           out  middle               curr                                         else                        out  middle             curr                                                        out  last                      trim  pieces 0                 out  suffix                    trim  pieces 2                break          default        unparseable             unset  pieces               break                 return  out            gt

User · Answer

Are you sure the Full Legal Name will always include First  Middle and Last  I know people that have only one name as Full Legal Name  and honestly I am not sure if that s their First or Last Name   -  I also know people that have more than one Fisrt names in their legal name  but don t have a Middle name  And there are some people that have multiple Middle names   Then there s also the order of the names in the Full Legal Name  As far as I know  in some Asian cultures the Last Name comes first in the Full Legal Name   On a more practical note  you could split the Full Name on whitespace and threat the first token as First name and the last token  or the only token in case of only one name  as Last name  Though this assumes that the order will be always the same

User · Answer

This query is working fine   SELECT name      Ltrim SubString name  1  Isnull Nullif CHARINDEX      name   0   1000    AS FirstName      Ltrim SUBSTRING name  CharIndex      name   CASE                  WHEN  CHARINDEX      name  CHARINDEX      name    1  - CHARINDEX      name    lt   0                     THEN 0                 ELSE CHARINDEX      name  CHARINDEX      name    1  - CHARINDEX      name                  END   AS MiddleName      Ltrim SUBSTRING name  Isnull Nullif CHARINDEX      name  Charindex      name    1   0   CHARINDEX      name    CASE                  WHEN Charindex      name    0                     THEN 0                 ELSE LEN name                  END   AS LastName FROM yourtableName

User · Answer

The work by  JosephStyons and  Digs is great  I used parts of their work to create a new function for SQL Server 2016 and newer  This one also handles suffixes  as well as prefixes   CREATE FUNCTION  dbo   NameParser         name nvarchar 100    RETURNS TABLE AS RETURN    WITH prep AS       SELECT          original    name          cleanName   REPLACE REPLACE REPLACE REPLACE LTRIM RTRIM  name                                             SELECT     prep original      aux prefix      firstName firstName      middleName middleName      lastName lastName      aux suffix FROM     prep     CROSS APPLY           SELECT              prefix                   CASE                      WHEN LEFT prep cleanName  3  IN   MR     MS     DR     FR                            THEN LEFT prep cleanName  2                      WHEN LEFT prep cleanName  4  IN   MRS     LRD     SIR                            THEN LEFT prep cleanName  3                      WHEN LEFT prep cleanName  5  IN   LORD     LADY     MISS     PROF                            THEN LEFT prep cleanName  4                      ELSE                    END              suffix                   CASE                      WHEN RIGHT prep cleanName  3  IN    JR     SR     II     IV                           THEN RIGHT prep cleanName  2                      WHEN RIGHT prep cleanName  4  IN    III     ESQ                           THEN RIGHT prep cleanName  3                      ELSE                    END       aux     CROSS APPLY           SELECT             baseName   LTRIM RTRIM SUBSTRING prep cleanName  LEN aux prefix    1  LEN prep cleanName  - LEN aux prefix  - LEN aux suffix                  numParts    SELECT COUNT 1  FROM STRING SPLIT LTRIM RTRIM SUBSTRING prep cleanName  LEN aux prefix    1  LEN prep cleanName  - LEN aux prefix  - LEN aux suffix                  core     CROSS APPLY           SELECT             firstName                    CASE                     WHEN core numParts  lt   1 THEN core baseName                     ELSE LEFT core baseName  CHARINDEX      core baseName  1  - 1                   END        firstName     CROSS APPLY           SELECT             remainder                    CASE                     WHEN core numParts  lt   1 THEN                        ELSE LTRIM SUBSTRING core baseName  LEN firstName firstName    1  999999                   END       work1     CROSS APPLY           SELECT             middleName                    CASE                     WHEN core numParts  lt   2 THEN                        ELSE LEFT work1 remainder  CHARINDEX      work1 remainder  1  - 1                   END       middleName     CROSS APPLY           SELECT             lastName                    CASE                     WHEN core numParts  lt   1 THEN                        ELSE LTRIM SUBSTRING work1 remainder  LEN middleName middleName    1  999999                   END       lastName    GO  SELECT   FROM dbo NameParser  Madonna   SELECT   FROM dbo NameParser  Will Smith   SELECT   FROM dbo NameParser  Neil Degrasse Tyson   SELECT   FROM dbo NameParser  Dr  Neil Degrasse Tyson   SELECT   FROM dbo NameParser  Mr  Hyde   SELECT   FROM dbo NameParser  Mrs  Thurston Howell  III

User · Answer

We of course all understand that there s no perfect way to solve this problem  but some solutions can get you farther than others     In particular  it s pretty easy to go beyond simple whitespace-splitters if you just have some lists of common prefixes  Mr  Dr  Mrs  etc    infixes  von  de  del  etc    suffixes  Jr  III  Sr  etc   and so on   It s also helpful if you have some lists of common first names  in various languages cultures  if your names are diverse  so that you can guess whether a word in the middle is likely to be part of the last name or not   BibTeX also implements some heuristics that get you part of the way there  they re encapsulated in the Text  BibTeX  Name perl module   Here s a quick code sample that does a reasonable job   use Text  BibTeX  use Text  BibTeX  Name   name    Dr  Mario Luis de Luigi Jr     name    s   s   dm rs    miss  s   i   dr  1   n Text  BibTeX  Name- gt new  name   print join   t    dr  map       n- gt part          qw first von last jr      n

User · Answer

Here s a stored procedure that will put the first word found into First Name  the last word into Last Name and everything in between into Middle Name   create procedure  dbo   import ParseName                     FullName nvarchar max        FirstName nvarchar 255  output       MiddleName nvarchar 255  output       LastName nvarchar 255   output   as begin  set  FirstName      set  MiddleName      set  LastName        set  FullName   ltrim rtrim  FullName    declare  ReverseFullName nvarchar max  set  ReverseFullName   reverse  FullName   declare  lengthOfFullName int declare  endOfFirstName int declare  beginningOfLastName int  set  lengthOfFullName   len  FullName  set  endOfFirstName   charindex       FullName  set  beginningOfLastName    lengthOfFullName - charindex       ReverseFullName    1  set  FirstName   case when  endOfFirstName  lt  gt  0                        then substring  FullName  1   endOfFirstName - 1                         else                     end  set  MiddleName   case when   endOfFirstName  lt  gt  0 and  beginningOfLastName  lt  gt  0 and  beginningOfLastName  gt   endOfFirstName                         then ltrim rtrim substring  FullName   endOfFirstName    beginningOfLastName -  endOfFirstName                            else                      end  set  LastName   case when  beginningOfLastName  lt  gt  0                       then substring  FullName   beginningOfLastName   1    lengthOfFullName -  beginningOfLastName                       else                    end  return  end    And here s me calling it   DECLARE  FirstName nvarchar 255            MiddleName nvarchar 255            LastName nvarchar 255   EXEC     dbo   import ParseName           FullName   N Scott The Other Scott Kowalczyk            FirstName    FirstName OUTPUT           MiddleName    MiddleName OUTPUT           LastName    LastName OUTPUT  print    FirstName  print    MiddleName print    LastName   output   Scott The Other Scott Kowalczyk

User · Answer

Employee table has column  Name  and we had to split it into First  Middle and Last Name  This query will handle to keep middle name as null if name column has value of two words like  James Thomas    UPDATE Employees SET  First Name    CASE          WHEN  len name  - len Replace name               2             THEN PARSENAME Name  3          WHEN  len name  - len Replace name               1             THEN PARSENAME Name  2          ELSE PARSENAME Name  1          END       Middle Name    CASE          WHEN  len name  - len Replace name               2             THEN PARSENAME Name  2          ELSE NULL         END       Last Name    CASE          WHEN  len name  - len Replace name               2             THEN PARSENAME Name  1          WHEN  len name  - len Replace name               1             THEN PARSENAME Name  1          ELSE NULL         END GO  UPDATE Employee SET  Name    Replace  Name             GO

User · Answer

I once made a 500 character regular expression to parse first  last and middle names from an arbitrary string  Even with that honking regex  it only got around 97  accuracy due to the complete inconsistency of the input  Still  better than nothing

User · Answer

Like  1 said  it s not trivial  Hyphenated last names  initials  double names  inverse name sequence and a variety of other anomalies can ruin your carefully crafted function   You could use a 3rd party library  plug disclaimer - I worked on this product    http   www melissadata com nameobject nameobject htm

User · Answer

I m not sure about SQL server  but in postgres you could do something like this   SELECT    SUBSTRING fullname      w     as firstname    SUBSTRING fullname     w   s   w    s  w    as middle    COALESCE SUBSTRING fullname     w   s  w   s   w      SUBSTRING fullname     w   s   w      as lastname FROM  public person   The regex expressions could probably be a bit more concise  but you get the point  This does by the way not work for persons having two double names  in the Netherlands we have this a lot  Jan van der Ploeg   so I d be very careful with the results

User · Answer

Based on  hajili s contribution  which is a creative use of the parsename function  intended to parse the name of an object that is period-separated   I modified it so it can handle cases where the data doesn t containt a middle name or when the name is  John and Jane Doe   It s not 100  perfect but it s compact and might do the trick depending on the business case   SELECT NAME  CASE WHEN parsename replace NAME             4  IS NOT NULL THEN     parsename replace NAME             4  ELSE     CASE WHEN parsename replace NAME             3  IS NOT NULL THEN      parsename replace NAME             3  ELSE    parsename replace NAME             2  end END as FirstName      CASE WHEN parsename replace NAME             3  IS NOT NULL THEN     parsename replace NAME             2  ELSE NULL END as MiddleName     parsename replace NAME             1  as LastName from    YourTableName

User · Answer

This Will Work in Case String Is FirstName MiddleName LastName  Select   DISTINCT NAMES       SUBSTRING NAMES   1  CHARINDEX      NAMES  - 1  as FirstName      RTRIM LTRIM REPLACE REPLACE NAMES SUBSTRING NAMES   1  CHARINDEX      NAMES  - 1      REVERSE  LEFT  REVERSE NAMES   CHARINDEX      REVERSE NAMES  -1          as MiddleName      REVERSE  LEFT  REVERSE NAMES   CHARINDEX      REVERSE NAMES  -1     as LastName  From TABLENAME

User · Answer

Alternative simple way is to use parsename    select full name     parsename replace full name             3  as FirstName     parsename replace full name             2  as MiddleName     parsename replace full name             1  as LastName  from YourTableName   source

User · Answer

Reverse the problem  add columns to hold the individual pieces and combine them to get the full name   The reason this will be the best answer is that there is no guaranteed way to figure out a person has registered as their first name  and what is their middle name   For instance  how would you split this   Jan Olav Olsen Heggelien   This  while being fictious  is a legal name in Norway  and could  but would not have to  be split like this   First name  Jan Olav Middle name  Olsen Last name  Heggelien   or  like this   First name  Jan Olav Last name  Olsen Heggelien   or  like this   First name  Jan Middle name  Olav Last name  Olsen Heggelien   I would imagine similar occurances can be found in most languages   So instead of trying to interpreting data which does not have enough information to get it right  store the correct interpretation  and combine to get the full name

User · Answer

Get a sql regex function  Sample  http   msdn microsoft com en-us magazine cc163473 aspx Extract names using regular expressions     I recommend Expresso for learnin building testing regular expressions  Old free version  new commercial version

User · Answer

Check this query in Athena for only one-space separated string  e g  first name and middle name combination    SELECT name  REVERSE  SUBSTR  REVERSE name   1  STRPOS REVERSE name            AS middle name  FROM name table  If you expect to have two or more spaces  you can easily extend the above query

User · Answer

Here is a self-contained example  with easily manipulated test data     With this example  if you have a name with more than three parts  then all the  extra  stuff will get put in the LAST NAME field   An exception is made for specific strings that are identified as  titles   such as  DR    MRS   and  MR    If the middle name is missing  then you just get FIRST NAME and LAST NAME  MIDDLE NAME will be NULL    You could smash it into a giant nested blob of SUBSTRINGs  but readability is hard enough as it is when you do this in SQL   Edit-- Handle the following special cases   1 - The NAME field is NULL  2 - The NAME field contains leading   trailing spaces  3 - The NAME field has   1 consecutive space within the name  4 - The NAME field contains ONLY the first name  5 - Include the original full name in the final output as a separate column  for readability  6 - Handle a specific list of prefixes as a separate  title  column  SELECT   FIRST NAME ORIGINAL INPUT DATA   FIRST NAME TITLE   FIRST NAME FIRST NAME   CASE WHEN 0   CHARINDEX     FIRST NAME REST OF NAME         THEN NULL  --no more spaces   assume rest is the last name        ELSE SUBSTRING                         FIRST NAME REST OF NAME                        1                        CHARINDEX     FIRST NAME REST OF NAME -1                               END AS MIDDLE NAME   SUBSTRING               FIRST NAME REST OF NAME              1   CHARINDEX     FIRST NAME REST OF NAME               LEN FIRST NAME REST OF NAME               AS LAST NAME FROM         SELECT     TITLE TITLE     CASE WHEN 0   CHARINDEX     TITLE REST OF NAME           THEN TITLE REST OF NAME --No space  return the whole thing          ELSE SUBSTRING                           TITLE REST OF NAME                          1                          CHARINDEX     TITLE REST OF NAME -1                              END AS FIRST NAME     CASE WHEN 0   CHARINDEX     TITLE REST OF NAME             THEN NULL  --no spaces   all   then 1st name is all we have          ELSE SUBSTRING                           TITLE REST OF NAME                          CHARINDEX     TITLE REST OF NAME  1                          LEN TITLE REST OF NAME                               END AS REST OF NAME     TITLE ORIGINAL INPUT DATA   FROM              SELECT       --if the first three characters are in this list        --then pull it as a  title    otherwise return NULL for title        CASE WHEN SUBSTRING TEST DATA FULL NAME 1 3  IN   MR    MS    DR    MRS              THEN LTRIM RTRIM SUBSTRING TEST DATA FULL NAME 1 3               ELSE NULL            END AS TITLE       --if you change the list  don t forget to change it here  too        --so much for the DRY prinicple          CASE WHEN SUBSTRING TEST DATA FULL NAME 1 3  IN   MR    MS    DR    MRS              THEN LTRIM RTRIM SUBSTRING TEST DATA FULL NAME 4 LEN TEST DATA FULL NAME                ELSE LTRIM RTRIM TEST DATA FULL NAME              END AS REST OF NAME       TEST DATA ORIGINAL INPUT DATA     FROM               SELECT         --trim leading  amp  trailing spaces before trying to process         --disallow extra spaces  within  the name         REPLACE REPLACE LTRIM RTRIM FULL NAME                       AS FULL NAME         FULL NAME AS ORIGINAL INPUT DATA       FROM                   --if you use this  then replace the following         --block with your actual table               SELECT  GEORGE W BUSH  AS FULL NAME         UNION SELECT  SUSAN B ANTHONY  AS FULL NAME         UNION SELECT  ALEXANDER HAMILTON  AS FULL NAME         UNION SELECT  OSAMA BIN LADEN JR  AS FULL NAME         UNION SELECT  MARTIN J VAN BUREN SENIOR III  AS FULL NAME         UNION SELECT  TOMMY  AS FULL NAME         UNION SELECT  BILLY  AS FULL NAME         UNION SELECT NULL AS FULL NAME         UNION SELECT     AS FULL NAME         UNION SELECT      JOHN  JACOB     SMITH  AS FULL NAME         UNION SELECT   DR  SANJAY       GUPTA  AS FULL NAME         UNION SELECT  DR JOHN S HOPKINS  AS FULL NAME         UNION SELECT   MRS  SUSAN ADAMS  AS FULL NAME         UNION SELECT   MS AUGUSTA  ADA   KING   AS FULL NAME                 RAW DATA         TEST DATA       TITLE     FIRST NAME

User · Answer

Unless you have very  very well-behaved data  this is a non-trivial challenge   A naive approach would be to tokenize on whitespace and assume that a three-token result is  first  middle  last  and a two-token result is  first  last   but you re going to have to deal with multi-word surnames  e g   Van Buren   and multiple middle names

User · Answer

I would do this as an iterative process     1   Dump the table to a flat file to work with   2   Write a simple program to break up your Names using a space as separator where firsts token is the first name  if there are 3 token then token 2 is middle name and token 3 is last name   If there are 2 tokens then the second token is the last name    Perl  Java  or C C    language doesn t matter   3   Eyeball the results   Look for names that don t fit this rule     4   Using that example  create a new rule to handle that exception     5   Rinse and Repeat  Eventually you will get a program that fixes all your data

User · Answer

The biggest problem I ran into doing this was cases like  Bob R  Smith  Jr    The algorithm I used is posted at http   www blackbeltcoder com Articles strings splitting-a-name-into-first-and-last-names  My code is in C  but you could port it if you must have in SQL

User · Answer

Subject to the caveats that have already been raised regarding spaces in names and other anomalies  the following code will at least handle 98  of names    Note  messy SQL because I don t have a regex option in the database I use      Warning  messy SQL follows   create table parsname  fullname char 50   name1 char 30   name2 char 30   name3 char 30   name4 char 40    insert into parsname  fullname  select fullname from ImportTable  update parsname set name1   substring fullname  1  locate      fullname     fullname   ltrim substring fullname  locate      fullname   length fullname     where locate      rtrim fullname    gt  0  update parsname set name2   substring fullname  1  locate      fullname     fullname   ltrim substring fullname  locate      fullname   length fullname     where locate      rtrim fullname    gt  0  update parsname set name3   substring fullname  1  locate      fullname     fullname   ltrim substring fullname  locate      fullname   length fullname     where locate      rtrim fullname    gt  0  update parsname set name4   substring fullname  1  locate      fullname     fullname   ltrim substring fullname  locate      fullname   length fullname     where locate      rtrim fullname    gt  0     fullname now contains the last word in the string  select fullname as FirstName     as MiddleName     as LastName from parsname where fullname is not null and name1 is null and name2 is null union all select name1 as FirstName  name2 as MiddleName  fullname as LastName from parsname where name1 is not null and name3 is null   The code works by creating a temporary table  parsname  and tokenizing the fullname by spaces   Any names ending up with values in name3 or name4 are non-conforming and will need to be dealt with differently

User · Answer

As everyone else says  you can t from a simple programmatic way   Consider these examples    President  George Herbert Walker Bush   First Middle Middle Last  Presidential assassin  John Wilkes Booth   First Middle Last  Guitarist  Eddie Van Halen   First Last Last  And his mom probably calls him Edward Lodewijk Van Halen  First Middle Last Last  Famed castaway  Mary Ann Summers   First First Last  New Mexico GOP chairman  Fernando C de Baca   First Last Last Last

[sql] SQL: parse the first, middle and last name from a fullname field

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to parsing