Split Field On Space If Exists

Inigo Montoya 586 Reputation points
2021-04-30T13:30:57.713+00:00

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')

Select
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 =

Transact-SQL
Transact-SQL
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
    2021-04-30T14:52:04.207+00:00

    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.

    SQL

    -- 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'),   
    ('Ted');  
    -- 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;  
    

    Output

    +----+----------------------+-----------+-----------------+  
    | 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
    2021-05-02T15:04:46.253+00:00

    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
    2021-05-03T06:03:31.327+00:00

    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]  
    

    Output:
    93224-image.png

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

    Regards
    Echo


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

    1 person found this answer helpful.
    0 comments No comments