sql - how to 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 retried 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

20

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

451,927
24
239
318

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 3 · 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 2 · Answer 4 · answered Feb 12 '19 at 17:34

2

BigQuery: Standard Sql

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

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

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 1 · Answer 5 · answered Mar 29 '18 at 15:06

1

I use this query to retrive 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

Resuls

answered Mar 29 '18 at 15:06

Luis Pinheiro

11
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 0 · Answer 6 · 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 0 · Answer 7 · 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,180
14
30
39

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 8 · 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 9 · 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

602
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 10 · answered Mar 15 '21 at 11:10

0

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

answered Mar 15 '21 at 11:10

Shivani Gupta

1

score 0 · Answer 11 · 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

6,191
35
44
70

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

score -3 · Answer 12 · 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

sql - how to split fullname into first and last name

12 Answers12

Linked