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?

Answer 1

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 ) ) ) > 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) <> ' I')  
 BEGIN  
  set @RetName = left(   
   CASE WHEN   
    charindex( ' ', reverse( ltrim( rtrim(   
    replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')  
    ) ) ) ) > 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.', '')  
    ) ) ) ) > 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

Answer 2

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;

Answer 3

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]

Answer 4

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. :)

Answer 5

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.

Answer 6

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, 8000) AS LastName
FROM
  whereever