Split Field On Space If Exists

Question

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 =

Answer 1

Hi @Johnathan Simpson

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       |                 |  
+----+----------------------+-----------+-----------------+  

Answer 2

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]

Answer 3

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:

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.