Split Field On Space If Exists

Inigo Montoya 586 Reputation points

I have a database where a name is in one field, how can I split this data on the space so I get a first name and last name value, but the caveat is that there is the case when there is no space so would only have a first name.

How can I achieve this?

Create Table Data
   FullName varchar(200)  

Insert Into Data Values ('Jason Jones'), ('Mark Smith'), ('Ted')

FirstName = 
,LastName =
From Data

My SQL Version is Microsoft SQL Server 2016 and my desired output would be
case 1 -
FirstName = Jason
LastName = Jones
case 2 -
FirstName = Mark
LastName = Smith
case 3 -
FirstName = Ted
LastName =

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,519 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,566 Reputation points

    Hi @Inigo Montoya

    Please try the following solution.
    I made it a little bit more sophisticated. First token is considered as a first name, anything else is a last name.


    -- DDL and sample data population, start  
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY,  FullName varchar(200));  
    INSERT INTO @tbl (FullName) VALUES  
    ('Jason Jones'),   
    ('Mark Smith'),   
    ('Liza Burkovski Smith'),   
    -- DDL and sample data population, end  
    DECLARE @separator CHAR(1) = SPACE(1);  
    ;WITH rs AS  
     SELECT *   
     , TRY_CAST('<root><r>' +   
     REPLACE(FullName, @separator, '</r><r>') +   
     '</r></root>' AS XML) AS xmldata  
     FROM @tbl  
    SELECT ID, rs.FullName   
     , rs.xmldata.value('(/root/r[1]/text())[1]', 'VARCHAR(30)') AS firstName  
     , rs.xmldata.query('data(/root/r[position() gt 1]/text())').value('.' , 'VARCHAR(100)') AS lastName  
    FROM rs;  


    | ID |       FullName       | firstName |    lastName     |  
    |  1 | Jason Jones          | Jason     | Jones           |  
    |  2 | Mark Smith           | Mark      | Smith           |  
    |  3 | Liza Burkovski Smith | Liza      | Burkovski Smith |  
    |  4 | Ted                  | Ted       |                 |  
    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points

    Try this:

    SELECT FullName, 
        CASE WHEN CHARINDEX(' ', FullName) > 0 THEN LEFT(FullName, CHARINDEX(' ', FullName) - 1) ELSE FullName END AS FirstName, 
        CASE WHEN CHARINDEX(' ', FullName) > 0 THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName) - CHARINDEX(' ', FullName)) ELSE '' END AS LastName
    FROM [Data]
    1 person found this answer helpful.
    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points

    Please also check:

    SELECT FullName,   
    IIF(CHARINDEX(' ',FullName)=0,FullName,LEFT(FullName, CHARINDEX(' ',FullName))) FirstName,   
    IIF(CHARINDEX(' ',FullName)=0,'',RIGHT(FullName,LEN(FullName)-CHARINDEX(' ',FullName))) LastName  
    FROM [Data]  


    If you have any question, please feel free to let me know.


    If the answer is helpful, please click "Accept Answer" and upvote it.

    1 person found this answer helpful.
    0 comments No comments