split fullname into lastname firstname middlename and suffix

how to split first name, middle name and last name in sql
how to split first name, middle name and last name in c#
how to split first name middle name and last name in oracle
how to split first name and last name in sql
how to separate first name and last name from full name in sql
how to display first name and last name in c#
how to split first name, middle name and last name in javascript
how to separate first name middle name and last name in excel using formula

How can I split fullname into Lastname Firstname Middlename suffix? eg: 1.Smith John T PHD 2.smith john 3.smith john T name is separated by space .this name column has these 3 differnt types.

Desired output:

1) Smith -- lastname John -- firstname T -- middlename Phd -- Suffix

2)SMITH--LASTNAME JOHN --FIRSTNAME

3) SMITH--LASTNAME JOHN --FIRSTNAME T--MIDDLENAME

If you are using SQL 2016 or Azure SQL or Azure SQL Datawarehouse, you can use string_split function. Read more on string_split

DECLARE @tmp TABLE (Name NVARCHAR(100))

INSERT @tmp
SELECT 'Smith John T PHD'

SELECT CASE 
        WHEN rn = 1
            THEN 'LastName'
        WHEN rn = 2
            THEN 'FirstName'
        WHEN rn = 3
            THEN 'Middlename'
        WHEN rn = 4
            THEN 'Suffix'
        END AS NameType
    ,NAMES.value
FROM (
    SELECT row_number() OVER (
            ORDER BY (
                    SELECT 0
                    )
            ) AS rn
        ,s.value
    FROM @tmp AS t
    CROSS APPLY STRING_SPLIT(t.Name, ' ') AS s
    ) AS NAMES

Split names in Excel: separate first and last name into different , If your names are in the First name Middle name Last name 1, Full name, First name, Last name, Suffix. If you need to be able to handle 3 word names of the form [First Name] [Middle Name] [Last Name] (as I suspect you do), you'll probably want to build a list of the possible suffixes and use that list to determine whether each 3 word name has a suffix or middle name accordingly.

         /*
            APPROACH:
            STEP 1: DECLARE FOUR VARIABLES TO HOLD -- LAST NAME, FIRST NAME, MIDDLE 
            NAME AND SUFFIX
            STEP 2: TRAVERSE THE STRING, AND KEEP A TRACK OF NUMBER OF SPACES YOU ARE 
            GETTING
             STEP 2.1 IF SPACE_NUM = 1, INITIALIZE THE STRINGS YOU ARE VISITED UPTO 
            NOW, INTO 'LAST NAME'
             STEP 2.2 IF SPACE_NUM = 2, INITIALIZE THE STRINGS YOU ARE VISITED UPTO 
            NOW, INTO 'FIRST NAME'
             STEP 2.3 IF SPACE_NUM = 3, INITIALIZE THE STRINGS YOU ARE VISITED UPTO 
            NOW, INTO 'MIDDLE NAME'
             STEP 3. INITIALIZE THE THE LAST STRINGS YOU VISITED INTO 'SUFFIX'

            NOTE: ASSUMING THE STRINGS ARE SAME FORMAT YOU MENTIONED.
            NOTE: FOR GETTING THE FIRST STRING A.K.A LAST NAME, WE HAVE TO PUT EXTRA- 
            SPACE IN FRONT OF STRING.
         */


         DECLARE @STRING AS VARCHAR(50) = 'Smith John T PHD'
         SET @STRING = ' '+@STRING

         DECLARE @FIRST_NAME AS VARCHAR(30) = ''
         DECLARE @LAST_NAME AS VARCHAR(30) = ''
         DECLARE @MIDDLE_NAME AS VARCHAR(30) = ''
         DECLARE @SUFFIX AS VARCHAR(30) = ''


         DECLARE @I AS INT = 1
         DECLARE @COUNT AS INT =0
         DECLARE @STR AS VARCHAR(20) =''

         WHILE @I <> LEN(@STRING)+1
         BEGIN
            IF SUBSTRING(@STRING, @I, 1) = ' '
            BEGIN
                SET @COUNT = @COUNT +1
                SET @I = @I +1
                CONTINUE
            END

            SET @STR += SUBSTRING(@STRING, @I, 1)

            IF @COUNT = 1
            BEGIN
                SET @LAST_NAME += @STR
                SET @STR = ''
            END

            IF @COUNT = 2
            BEGIN
                SET @FIRST_NAME += @STR
                SET @STR = ''
            END

            IF @COUNT = 3
            BEGIN
                SET @MIDDLE_NAME += @STR
                SET @STR = ''
            END
        SET @I = @I +1
        END

   SET @SUFFIX = @STR

    SELECT @FIRST_NAME AS "FIRST NAME", @LAST_NAME AS "LAST NAME", @MIDDLE_NAME 
     "Middle Name", @SUFFIX as "Suffix";

Extracting Title, First Name, Middle Name, Last Name from a field in , Parsing a "FullName" field that contains a Title, First Name, Middle Name, Last Name, and/or suffix into a returned separated string which can be used  Split Name Into First, Middle and Last Name In Sql Server Last Week one of my Friend ask "how to split name into first middle and last name.” Over this he also said he tried it but its very confusing and complex So I Thought of writing a blog on it.

If the fullname always in the specified format, then you can do :

select parsename(newcol, 4), parsename(newcol, 3), parsename(newcol, 2), parsename(newcol, 1)
from t cross apply
    ( values (replace(col, ' ', '.')) 
    ) tt(newcol);

Splitting first name, middle initial, last name, and suffix into two strings, I have a list of user's names formatted as [firstname lastname], which I intend to Equals(suffix)){ lastname = fullname.Split().Reverse().Take(2). surname, middle name(s), title, post-nominals, and so on, as separate fields  The suffix does not represent the last name and so if IsSuffix() returns true, Split() calls FindWordStart() again to parse the second-to-last space-delimited token from the name. The first and last names are then formed by splitting the name at the final position that was parsed.

Try this

DECLARE @tmp TABLE (Name NVARCHAR(100))

INSERT @tmp
SELECT 'Smith John T PHD'

SELECT dtt.Name,dt2.SplitedName FROM
(
SELECT  ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS Id,
        Split.a.value('.','nvarchar(1000)') AS Name

FROM(
        SELECT CAST('<S>'+REPLACE(Name,' ','</S><S>')+'</S>' AS XML) Name
         FROM @tmp
    )dt 
CROSS APPLY Name.nodes('S') AS Split(a))dtt
INNER JOIN 
(   SELECT 1 AS id,  'lastname' AS SplitedName UNION ALL
        SELECT 2 ,  'firstname'  UNION ALL
        SELECT 3 ,  'middlename '  UNION ALL
        SELECT 4 ,  'Suffix' 
)dt2 ON dt2.ID=dtt.Id

Result

Name    SplitedName
-------------------
Smith   lastname
John    firstname
T       middlename 
PHD     Suffix

SQL -Split name according to firstname,middlename,lastname,suffix , I have a column for Name which comes from the application to database. But I need to split the name as per firstname,middlename,lastname,suffix. ID, FullName, max(case when WordNum = 1 then Word end) as FirstName,  I need help splitting a Full Name into a First Name, Middle (if it exists), Last Name, and Suffix (if it exists). I'm close, but I'm having trouble getting all the possible combinations to work out correctly. This is what I have so far: SELECT SUBSTR(full_name,0,INSTR(full_name,' ')- 1) AS first_name

Split Name, The Split Name tool is an easy way to separate full name fields into first name, middle name, last name, and suffix fields. With split(id, n, start, pos) as( SELECT id, 1, 0, CHARINDEX(' ', fullname) FROM @names UNION ALL SELECT n.id, n+1, pos+1, CHARINDEX(' ', fullname, pos+1) FROM @names n INNER JOIN split s ON n.id = s.id WHERE CHARINDEX(' ', fullname, start+1) > 0 ) SELECT id , lastName = MAX(CASE WHEN v.n = 1 THEN v.val END) , firstname = MAX(CASE WHEN v.n = 2 THEN v.val END) , middleName = MAX(CASE WHEN v.n = 3 THEN v.val END) , title = MAX(CASE WHEN v.n = 4 THEN v.val END) FROM ( SELECT n.id, s.n

How to split full name to first and last name in Excel?, Hi Demi! To separate any name with a First Name, Middle Name(Initial), Last Name and Suffix, please use the below formula in the designated  So far, I have identified 11 different combinations of last name, first name, middle initial and suffix. What is the best way to split the name into its respective last name, first name, middle initial and suffix columns? I have attempted several coding changes to split the names, but I believe the result at the best is %85-90 accurate.

Can you please help with Hive Query to get FirstName, MiddleName , to get FirstName, MiddleName, LastName and Suffix from FullName in You can get first name, middle name, last name and suffix from full 

Comments
  • What version of SQL Server are you using?
  • Do all names have this format? Note that in general what you are trying to do is very difficult, especially in a database like SQL Server.
  • can you share some data for fullname
  • "Van Dyke" is a lastname. "Billy Bob" is a firstname. What do you propose for names components that contain spaces? What you are asking for may be more difficult than you currently assume. Don't forget "Charles Jnr." or "Charles Snr." either.
  • I'd get an actual person to go through everything and update the new columns (because you should be storing this data separately) with the information. If the name in ambiguous, contact the person to find out. If you have millions of rows, that's going to take a long time, but otherwise you are definitely going to make wrong assumptions.