Split fullname into first and last name

Question

If I have a table with a column that contains fullnames such as:

fullname
------------
Joe Bloggs
Peter Smith
Mary Jones and Liz Stone

How can I retrieve the first and last name from each of the entries in the full name column using SQL. I'm not worried about the second name in the 3rd entry in my example i.e. Liz Stone.

So basically to retrieve

Firstname
---------
Joe
Peter
Mary

Lastname  
--------
Bloggs 
Smith
Jones

What about middle names, such as `John Phillips Souza` ? Unless you are certain that each name will only contain a single first and last name, this problem can be difficult. — Tim Biegeleisen, Jan 04 '17 at 11:09
Why `Jones` and `Stone` instead of `Jones`, `and`, `Liz`, `Stone`? Splitting strings is forgivable for simple names, surnames. In this case though it appears you have some serious problems, with multiple persons stored in the same row — Panagiotis Kanavos, Jan 04 '17 at 11:14
i just don't need the second (partner's name) I can find everything I need with Mary Jones — thegunner, Jan 04 '17 at 11:17
@PanagiotisKanavos Sadly this is not an uncommon issue - usually when someone creates a database with limited knoweldge. I have this exact same issue to face when i begin migrating data within my own project. Currently my only real solution is to ADHOC the offending entries. — Takarii, Jan 04 '17 at 11:22
@thegunner I have a similar issue to face myself. I did stumble upon [this](http://stackoverflow.com/questions/159567/sql-parse-the-first-middle-and-last-name-from-a-fullname-field) solution a couple of years back. You might be able to modify it to suit your needs, however your use case is far from simple - I sympathise — Takarii, Jan 04 '17 at 11:24

Tim Biegeleisen · Answer 1 · 2017-01-04T11:20:34.603

27

Here is a pre SQL Server 2016 method, which uses basic string functions to isolate the first and last names.

SELECT SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) AS Firstname,     
       SUBSTRING(fullname,
                 CHARINDEX(' ', fullname) + 1,
                 LEN(fullname) - CHARINDEX(' ', fullname)) AS Lastname
FROM yourTable

Note that this solution assumes that the fullname column only contains a single first name and a single last name (i.e. no middle names, initials, etc.).

edited Jan 04 '17 at 11:20

answered Jan 04 '17 at 11:18

Tim Biegeleisen

502,043
27
286
360

Firstname comes out ok, but the Last name also include the partner's name e.g. 'Jones and Liz Stone' – thegunner Jan 04 '17 at 11:20
2

Breaking the `and` row into two records would probably require a custom function. I would recommend cleaning up your data before bringing it into SQL Server if you really have such edge cases. – Tim Biegeleisen Jan 04 '17 at 11:22
Could we just replace the "AND" and whatever name is after it with a blank string in your last name sql ? – thegunner Jan 04 '17 at 11:25
Could we just replace the "AND" and whatever name is after it with a blank string in the sql section were you deduce the last name? – thegunner Jan 04 '17 at 11:39

score 4 · Answer 2 · answered Jan 04 '17 at 12:20

This is a slippery slope and there are no easy answers. That said, consider the following

Declare @YourTable table (FullName varchar(50))
Insert Into @YourTable values
('Joe Bloggs'),
('Peter Smith'),
('Betty Jane Martinez'),
('Mary Jones and Liz Stone')


Select A.*
      ,FirstName = Pos1+case when Pos3 is not null then ' '+Pos2 else '' end
      ,LastName  = case when Pos3 is null then Pos2 else Pos3 end
 From @YourTable A
 Cross Apply (
    Select Pos1 = xDim.value('/x[1]','varchar(max)')
          ,Pos2 = xDim.value('/x[2]','varchar(max)')
          ,Pos3 = xDim.value('/x[3]','varchar(max)')
          ,Pos4 = xDim.value('/x[4]','varchar(max)')
          ,Pos5 = xDim.value('/x[5]','varchar(max)')
          ,Pos6 = xDim.value('/x[6]','varchar(max)')
     From  (Select Cast('<x>' + replace((Select substring(FullName,1,charindex(' and ',FullName+' and ')-1) as [*] For XML Path('')),' ','</x><x>')+'</x>' as xml) as xDim) as A 
 ) B

Returns

FullName                    FirstName   LastName
Joe Bloggs                  Joe         Bloggs
Peter Smith                 Peter       Smith
Betty Jane Martinez         Betty Jane  Martinez
Mary Jones and Liz Stone    Mary        Jones

If it helps with the visual, the CROSS APPLY generates

score 4 · Answer 3 · answered Feb 05 '19 at 13:29

SELECT CASE
           WHEN CHARINDEX(' ', FullName) > 0
           THEN SUBSTRING(FullName, 1, LEN(FullName) - CHARINDEX(' ', REVERSE(FullName)))
           ELSE ''
       END AS FirstName,
       CASE
           WHEN CHARINDEX(' ', FullName) > 0
           THEN REVERSE(SUBSTRING(REVERSE(FullName), 
                       1, 
                       CHARINDEX(' ', REVERSE(FullName)) - 1))
           ELSE FullName
       END AS LastName
FROM(VALUES('Mary Anne Bloggs'), ('Joe Bloggs'), ('Bloggs')) AS T(FullName);

This version checks that there is a space in the full name to split on. If there isn't then the first name is set to an empty string and the full name is put into the surname. Also, reverse is employed to split on the last space when there is more than one space

score 3 · Answer 4 · edited Oct 29 '22 at 20:17

3

I use this query to retrieve first and lastname

SELECT
    SUBSTRING(FULLNAME, 1, CASE WHEN CHARINDEX(' ', FULLNAME)>0 THEN CHARINDEX(' ', FULLNAME) - 1 ELSE LEN(FULLNAME) END ) AS Firstname,
    REVERSE(SUBSTRING(REVERSE(FULLNAME), 1, CASE WHEN CHARINDEX(' ', REVERSE(FULLNAME))>0 THEN CHARINDEX(' ', REVERSE(FULLNAME)) - 1 ELSE LEN(REVERSE(FULLNAME)) END ) ) AS Firstname
FROM HRMDESFO.EMPLOID

Results

edited Oct 29 '22 at 20:17

Dale K

25,246
15
42
71

answered Mar 29 '18 at 15:06

Luis Pinheiro

31
1

If query result is a text it should be posted as **a text** and not as **a screenshot** – Nikolai Shevchenko Mar 29 '18 at 15:24
I noticed the Firstname Alias is used for both replacements. I was able to use your query but had to change the Alias for the second replacement AS Lastname – randyh22 Dec 02 '21 at 20:02

score 2 · Answer 5 · edited Oct 29 '22 at 20:16

2

BigQuery: Standard SQL

substr(name,1,STRPOS(name,' ')-1) as FirstName,

substr(name,STRPOS(name,' ')+1,length(name)) as LastName

edited Oct 29 '22 at 20:16

Dale K

25,246
15
42
71

answered Feb 12 '19 at 17:34

Rishabh Dimri

31
2

While this answer is not wrong, it doesn't mention the SQL dialect used. String functions are very specific to SQL dialect: [SQL Dialects Reference/Functions and expressions/String functions](https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Functions_and_expressions/String_functions) – Alexander Ryzhov Feb 12 '19 at 18:51
I mentioned BigQuery: Standard Sql dialect in the heading. – Rishabh Dimri Feb 13 '19 at 19:25

score 0 · Answer 6 · edited Feb 05 '19 at 16:40

0

select  passemail,substring(passemail,1,instr(passemail,'@') - 1) as name ,
substring(passemail,instr(passemail,'@') + 1,length(passemail)) from passenger

edited Feb 05 '19 at 16:40

Robert Columbia

6,313
15
32
40

answered Feb 05 '19 at 16:39

user11018831

1

Please add an explanation to your answer to help others learn. Code only answers are considered impolite. – Brian Tompsett - 汤莱恩 Feb 05 '19 at 19:20

score 0 · Answer 7 · answered Feb 05 '20 at 15:46

For getting firstName

SELECT SUBSTR(FULLNAME,1,(LOCATE(' ',FULLNAME)))  AS FIRSTTNAME from EmployeeDetails;

FOR LASTNAME

SELECT SUBSTR(FULLNAME,(LOCATE(' ',FULLNAME)))  AS LASTNAME from EmployeeDetails;

SO

SELECT  SUBSTR(FULLNAME,1,(LOCATE(' ',FULLNAME)))  AS FIRSTTNAME, SUBSTR(FULLNAME,(LOCATE(' ',FULLNAME)))  AS LASTNAME from EmployeeDetails;

score 0 · Answer 8 · edited Feb 28 '20 at 15:50

0

SELECT 
  LEFT(column_name,  POSITION(' ' IN column_name)-1)                     AS first_name,
  RIGHT(column_name, LENGTH(column_name) - POSITION(' ' IN column_name)) AS last_name
FROM table_name

edited Feb 28 '20 at 15:50

Bryant

622
4
18

answered Feb 28 '20 at 14:10

Afro_giant

1

column_name: is the column containing the names. table_name: is the name of the table from which the column_name is selected from – Afro_giant Feb 28 '20 at 14:11
1

Please do your best to format your answer better by adding description. – Mort Feb 28 '20 at 15:19

score 0 · Answer 9 · edited Oct 29 '22 at 20:16

0

SELECT
    SUBSTR(NAME,1,(LOCATE(NAME, ' '))) AS FIRSTTNAME
    , SUBSTR(NAME,(LOCATE(NAME, ' ')+1)) AS LASTNAME
FROM yourTABLE;

edited Oct 29 '22 at 20:16

Dale K

25,246
15
42
71

answered Mar 15 '21 at 11:10

Shivani Gupta

1

score 0 · Answer 10 · edited May 14 '22 at 06:53

0

SELECT SUBSTRING(candidate_name, 1, CASE WHEN CHARINDEX(' ', candidate_name)>0   THEN CHARINDEX(' ', candidate_name) - 1   
ELSE LEN(candidate_name) END ) AS Firstname,
SUBSTRING(substring(candidate_name,CHARINDEX(' ', candidate_name)+1,LEN(candidate_name)), 1, 
CASE WHEN CHARINDEX(' ', candidate_name)>1 THEN CHARINDEX(' ', substring(candidate_name,CHARINDEX(' ', candidate_name)+1,LEN(candidate_name))) 
ELSE null END ) AS middle_name,
REVERSE(SUBSTRING(REVERSE(candidate_name), 1, 
 CASE WHEN CHARINDEX(' ', REVERSE(candidate_name))>0 
 THEN CHARINDEX(' ', REVERSE(candidate_name)) - 1
 ELSE null END ) )AS last_name
FROM  Test_name

edited May 14 '22 at 06:53

Jeremy Caney

7,102
69
48
77

answered May 13 '22 at 11:17

Bablu Prasad Nayak

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Jeremy Caney May 14 '22 at 06:53

Abhishek Kumar · Answer 11 · 2022-09-11T08:33:38.317

So first we have to find the index for space(" ") because space is the character which is separating the two words ( first_name+" "+last_name). In my case, its mid_index is a variable that stores the index for space(" ").

SELECT primary_poc, STRPOS(fullname,' ') AS "mid_index"
   FROM yourTable_name

*Now we will use min_index to find the left and right sides of words. For this, we can use a subquery. Below is the final query *

SELECT fullname,
LEFT(fullname, mid_index - 1) AS "first_name",
RIGHT(fullname, LENGTH(primary_poc) - mid_index) AS "last_name"
FROM
(
   SELECT primary_poc, STRPOS(fullname,' ') AS "mid_index"
   FROM yourTable_name
) AS t1

Please edit your answer to include a description of what your code does. — Sean, Sep 10 '22 at 21:51

score 0 · Answer 12 · answered Nov 15 '22 at 10:44

FOR SQL SERVER

SELECT 
    SUBSTRING(fullname, 0, CHARINDEX(' ', fullname)) AS FirstName
    ,SUBSTRING(fullname, CHARINDEX(' ', fullname), LEN(fullname)) AS LastName
FROM [YourTable]

If your full name has another delimiter aside from space, such as dashes, you substitute in the dash e.g

SELECT 
        SUBSTRING(fullname, 0, CHARINDEX('-', fullname)) AS FirstName
        ,SUBSTRING(fullname, CHARINDEX('-', fullname), LEN(fullname)) AS LastName
    FROM [YourTable]

guzel6031 · Answer 13 · 2022-12-12T10:30:23.117

0

In Postgres SQL

SELECT fullname,
SUBSTRING(fullname, 1, POSITION(' ' IN fullname) - 1) as first_name  ,
SUBSTRING(fullname,(position(' 'in fullname)))  AS lastname from details;

edited Dec 12 '22 at 10:30

answered Dec 12 '22 at 10:13

guzel6031

11
4

Jim Denny · Answer 14 · 2023-07-13T17:47:00.047

--This works for data which may have a middle name, middle initial or just a first and last name. I am not dealing with suffixes (i.e. Jr, Sr, III, etc) and I am assuming data is always first, middle, last. The answer box doesn't like CTE's so I am submitting it with a subquery.

SELECT [payerName],
       frst, scnd, thrd,
       LEFT(payerName, CHARINDEX(' ', payerName)) AS FirstName,
       CASE WHEN Scnd > 0 
           THEN SUBSTRING(payerName, frst + 1, scnd - frst) 
           ELSE '' END AS MiddleName,
       CASE WHEN Scnd = 0 THEN SUBSTRING(payerName, thrd + 1, 50)
           ELSE SUBSTRING(payerName, scnd + 1, 50)
           END AS LastName
    FROM (
        SELECT [payerName],
           LEFT(payerName, CHARINDEX(' ', payerName)) AS FirstName,
           CHARINDEX(' ', TRIM(payerName)) AS Frst,
           CHARINDEX(' ', TRIM(payerName), CHARINDEX(' ', TRIM(payerName))+1) AS Scnd,
           CHARINDEX(' ', TRIM(payerName), CHARINDEX(' ', TRIM(payerName)) + CHARINDEX(' ', TRIM(payerName), CHARINDEX(' ', TRIM(payerName))+1)) AS Thrd
        FROM [AccountDeduction]
    ) rex

Result:

payerName frst scnd thrd FirstName MiddleName LastName

Grant E Corbin 6 8 0 Grant E Corbin

Alcena Winston 7 0 7 Alcena Winston

Jeff Crosby 5 0 5 Jeff Crosby

Carolyn Tillage 8 0 8 Carolyn Tillage

score -1 · Answer 15 · edited Sep 18 '17 at 14:13

This is the easiest and shortest to this question without any assumptions. Also you can even further enhance this with a rtrim(ltrim('firstname lastname')).

Just in case of any spaces before the strings,

Select 
    substring('Firstname Lastname',1,CHARINDEX(' ', 'Firstname Lastname')) as firstname, 
    substring('Firstname Lastname',CHARINDEX(' ', 'Firstname Lastname'),LEN('Firstname Lastname')) as Lastname

score -3 · Answer 16 · answered Jan 04 '17 at 11:13

-3

You can use,

 STRING_SPLIT (string , separator)

answered Jan 04 '17 at 11:13

Achu S

137
9

3

This should be a comment. If you want to post this as an answer, you should post an actual example. How is the OP going to access the surname? – Panagiotis Kanavos Jan 04 '17 at 11:15
or in the case also mentioned of "mr smith and mrs jane" or "Alex smith and alice jane" – Takarii Jan 04 '17 at 11:25

Split fullname into first and last name

16 Answers16

Linked