T-SQL substring - separating first and last name

Question

I have a column which has FirstName and LastName together. I'm writing a report to separate the FirstName And LastName. How do I get the FirstName and LastName separated in T-SQL?

Is there always a space? What happens for `Prince`, `Alf` or `Madonna`? How about `Philip Seymour Hoffman` or `James van der Beek`? — Aaron Bertrand, Jun 06 '12 at 20:24
Really, this is a **very** difficult task. To do it correctly, you are gonna need a names dictionary, or something to compare your input, and then decide how to separate them. Its a really complex thing to do — Lamak, Jun 06 '12 at 20:31

score 58 · Answer 1 · edited Mar 30 '20 at 18:35

58

Assuming the FirstName is all of the characters up to the first space:

SELECT
  SUBSTRING(username, 1, CHARINDEX(' ', username) - 1) AS FirstName,
  SUBSTRING(username, CHARINDEX(' ', username) + 1, LEN(username)) AS LastName
FROM
  whereever

edited Mar 30 '20 at 18:35

Community

1
1

answered Jun 06 '12 at 20:15

bluevector

3,485
1
15
18

2

and maybe you want to use LEN(username) instead of just 8000 – libjup Jun 06 '12 at 20:16
23

@libjup there's actually pretty good reason to use a fixed value that is higher than any possible length. Why bother calculating the length on every single row? Wasted cycles. – Aaron Bertrand Jun 06 '12 at 20:23
4

You can improve on this with a case when index 0 , to allow for when they only have a firstname. – John Nov 30 '15 at 08:55

score 23 · Answer 2 · answered Mar 07 '13 at 11:22

23

The easiest way I can find to do it is:

SELECT 
  SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1) AS FirstName,
  REVERSE(SUBSTRING(REVERSE(FullName), 1, CHARINDEX(' ', REVERSE(FullName)) - 1)) AS LastName
FROM
  [PERSON_TABLE]

answered Mar 07 '13 at 11:22

Rui de Almeida

331
2
3

2

I prefer this solution, since it removes the middle name from the full name – Capilé Jun 16 '14 at 14:47
3

That's my favourite answer too. Although, if you want your FirstName to contain the middle name(s) too (if you take into account that the LastName is a single noun), you could do something like: `REPLACE(FullName, REVERSE(SUBSTRING(REVERSE(FullName), 1, CHARINDEX(' ', REVERSE(FullName)) - 1)), '') AS FirstName` – actaram Mar 25 '15 at 12:48

Tim Schmelter · Answer 3 · 2012-06-06T20:30:44.733

16

This should work:

Select  
    LTRIM(RTRIM(SUBSTRING(FullName, 0, CHARINDEX(' ', FullName)))) As FirstName
,   LTRIM(RTRIM(SUBSTRING(FullName, CHARINDEX(' ', FullName)+1, 8000)))As LastName
FROM TABLE

Edit: Adopted Aaron's and Jonny's hint with the fixed length of 8000 to avoid unnecessary calculations.

edited Jun 06 '12 at 20:30

answered Jun 06 '12 at 20:14

Tim Schmelter

450,073
74
686
939

1

If there is no space in the original name, this will result in first name being empty, and last name being the value. – Evan Nov 17 '17 at 19:31

score 10 · Answer 4 · answered Aug 24 '12 at 22:47

validate last name is blank

SELECT  
person.fullName,
(CASE WHEN 0 = CHARINDEX(' ', person.fullName) 
    then  person.fullName 
    ELSE SUBSTRING(person.fullName, 1, CHARINDEX(' ', person.fullName)) end) as first_name,  
(CASE WHEN 0 = CHARINDEX(' ', person.fullName) 
    THEN ''  
    ELSE SUBSTRING(person.fullName,CHARINDEX(' ', person.fullName), LEN(person.fullName) )end) last_name

FROM person

score 7 · Answer 5 · edited May 31 '19 at 03:59

7

You could do this if firstname and surname are separated by space:

SELECT SUBSTRING(FirstAndSurnameCol, 0, CHARINDEX(' ', FirstAndSurnameCol)) Firstname,
SUBSTRING(FirstAndSurnameCol, CHARINDEX(' ', FirstAndSurnameCol)+1, LEN(FirstAndSurnameCol)) Surname FROM ...

edited May 31 '19 at 03:59

SteveCav

6,649
1
50
52

answered Jun 06 '12 at 20:13

libjup

4,019
2
18
23

3

SUBSTRING(FirstAndSurnameCol, CHARINDEX(' ', FirstAndSurnameCol)+1, LEN(FirstAndSurnameCol)) – André Morales Jan 11 '18 at 21:48
fixed for the impatient copy and pasters like me :) – SteveCav May 31 '19 at 03:59
How would you accomplish this if "Jr" is sometimes, but definitely not always, after the last name, or surname? Also some names have middle names, some have two middles names, etc. – Bryan Sep 24 '20 at 18:06

score 7 · Answer 6 · answered Jul 23 '14 at 02:43

Here is a more elaborated solution with a SQL function:

GetFirstname

CREATE FUNCTION [dbo].[ufn_GetFirstName]  
(  
 @FullName varchar(500)  
)  
RETURNS varchar(500)  
AS  
BEGIN  
 -- Declare the return variable here  
 DECLARE @RetName varchar(500)  

 SET @FullName = replace( replace( replace( replace( @FullName, '.', '' ), 'Mrs', '' ), 'Ms', '' ), 'Mr', '' )  

 SELECT   
  @RetName =   
    CASE WHEN charindex( ' ', ltrim( rtrim( @FullName ) ) ) &gt; 0 THEN left( ltrim( rtrim( @FullName ) ), charindex( ' ', ltrim( rtrim( @FullName  ) ) ) - 1 ) ELSE '' END  

 RETURN @RetName  
END

GetLastName

CREATE FUNCTION [dbo].[ufn_GetLastName]  
(  
 @FullName varchar(500)  
)  
RETURNS varchar(500)  
AS  
BEGIN  
 DECLARE @RetName varchar(500)  

 IF(right(ltrim(rtrim(@FullName)), 2) &lt;&gt; ' I')  
 BEGIN  
  set @RetName = left(   
   CASE WHEN   
    charindex( ' ', reverse( ltrim( rtrim(   
    replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')  
    ) ) ) ) &gt; 0   
   THEN   
    right( ltrim( rtrim(   
    replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')  
    ) ) , charindex( ' ', reverse( ltrim( rtrim(   
    replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')  
    ) ) )  ) - 1 )   
   ELSE '' END  
  , 25 )  
 END  
 ELSE  
 BEGIN  
  SET @RetName = left(   
   CASE WHEN   
    charindex( ' ', reverse( ltrim( rtrim(   
    replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')  
    ) ) ) ) &gt; 0   
   THEN   
    right( ltrim( rtrim(   
    replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')  
    ) ) , charindex( ' ', reverse( ltrim( rtrim(   
    replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')  
    ) ) )  ) - 1 )   
   ELSE '' END  
  , 25 )  
 END  

 RETURN @RetName  
END

USE:

SELECT dbo.ufn_GetFirstName(Fullname) as FirstName, dbo.ufn_GetLastName(Fullname) as LastName FROM #Names

I changed your replacement order so it would not leave a dot: CASE WHEN charindex(' ', reverse(ltrim(rtrim(replace(replace(replace(replace(replace(replace(@FullName, ' Jr.', '' ),' III', '' ),' II', '' ),' Jr', '' ),' Sr.', ''),'Sr', ''))))) > 0 — AdamRoof, Oct 19 '18 at 21:19

score 6 · Answer 7 · answered Nov 22 '12 at 19:15

This will take care of names like "Firstname Z. Lastname" and "First Z Last"

SELECT
CASE 
    WHEN CHARINDEX(' ',name) = 0 THEN name
    WHEN CHARINDEX(' ',name) = PATINDEX('% _[., ]%',name) THEN RTRIM(SUBSTRING(name, 1, CHARINDEX(' ',name) + 2)) 
    ELSE SUBSTRING(name,1, CHARINDEX(' ',name))
END [firstname]
,CASE 
    WHEN CHARINDEX(' ',name) = 0 THEN ''
    WHEN CHARINDEX(' ',name) = PATINDEX('% _[., ]%',name) THEN LTRIM(SUBSTRING(name, CHARINDEX(' ',name) + 3,1000)) 
    ELSE SUBSTRING(name,CHARINDEX(' ',name)+1,1000)
END [lastname]
FROM [myTable]

score 6 · Answer 8 · answered Jan 14 '16 at 14:37

I think below query will be helpful to split FirstName and LastName from FullName even if there is only FirstName. For example: 'Philip John' can be split into Philip and John. But if there is only Philip, because of the charIndex of Space is 0, it will only give you ''.

Try the below one.

declare @FullName varchar(100)='Philp John'

Select  
    LTRIM(RTRIM(SUBSTRING(@FullName, 0, CHARINDEX(' ', @FullName+' ')))) As FirstName
,   LTRIM(RTRIM(SUBSTRING(@FullName, CHARINDEX(' ', @FullName+' ')+1, 8000)))As LastName

Hope this will help you. :)

Chris H · Answer 9 · 2014-11-26T13:46:30.550

3

You may have problems if the Fullname doesn't contain a space. Assuming the whole of FullName goes to Surname if there is no space and FirstName becomes an empty string, then you can use this:

SELECT
  RTRIM(LEFT(FullName, CHARINDEX(' ', FullName))) AS FirstName,
  SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 8000) AS LastName
FROM
  MyNameTable;

edited Nov 26 '14 at 13:46

answered Nov 14 '14 at 10:23

Chris H

501
1
4
11

score 2 · Answer 10 · answered Jun 10 '15 at 02:05

The code below works with Last, First M name strings. Substitute "Name" with your name string column name. Since you have a period as a final character when there is a middle initial, you would replace the 2's with 3's in each of the lines (2, 6, and 8)- and change "RIGHT(Name, 1)" to "RIGHT(Name, 2)" in line 8.

SELECT  SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1) LastName ,
CASE WHEN LEFT(RIGHT(Name, 2), 1) <> ' '
     THEN LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99))
     ELSE LEFT(LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)),
               LEN(LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)))
               - 2)
END FirstName ,
CASE WHEN LEFT(RIGHT(Name, 2), 1) = ' ' THEN RIGHT(Name, 1)
     ELSE NULL
END MiddleName

score 1 · Answer 11 · answered Jun 30 '21 at 14:50

Let's suppose your table has Name column and it contains data like -

        Random Person
        FIRST LAST
        Alpha Beta

The query will be like this

    SELECT
LEFT(NAME,CHARINDEX(' ',NAME))                      AS      "First Name",
LTRIM(RTRIM(SUBSTRING(NAME,CHARINDEX(' ',NAME),100)))       AS       "Last Name"

FROM YOUR_TABLE

score 0 · Answer 12 · answered Dec 06 '19 at 03:02

For the last name as in US standards (i.e., last word in the [Full Name] column) and considering first name to include a possible middle initial, middle name, etc.:

SELECT DISTINCT
             [Full Name]
            ,REVERSE([Full Name])                   --  to visualize what the formula is doing
            ,CHARINDEX(' ', REVERSE([Full Name]))   --  finds the last space in the string
            ,[Last Name]    =   REVERSE(RTRIM(LTRIM(LEFT(REVERSE([Full Name]), CHARINDEX(' ', REVERSE([Full Name]))))))
            ,[First Name]   =   RTRIM(LTRIM(LEFT([Full Name], LEN([Full Name]) - CHARINDEX(' ', REVERSE([Full Name])))))

FROM ...

Note that this assumes [Full Name] has no spaces before or after the actual string. Otherwise, use RTRIM and LTRIM to remove these.

If first name is only the first word, use: [First Name] = RTRIM(LTRIM(LEFT([Full Name], CHARINDEX(' ', [Full Name])))) — Marcos, Dec 06 '19 at 03:03

T-SQL substring - separating first and last name

12 Answers12

Linked

Related