question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked EchoLiu-msft commented

Split Field On Space If Exists

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 =

sql-server-transact-sql
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What if it's with a double first name like "Marie Ann Miller" or a double last name?

0 Votes 0 ·

There is possibility of this, but chances are very very low.

0 Votes 0 ·

@InigoMontoya-1790,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

@YitzhakKhabinsky-0887 - updated OP for further information.

0 Votes 0 ·

@InigoMontoya-1790,

What's the latest?
Did you have a chance to to try the proposed solution?

0 Votes 0 ·

Have you tried the following methods? Could you have any updates?

Echo

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @InigoMontoya-1790

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       |                 |
 +----+----------------------+-----------+-----------------+
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered

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]
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

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.


image.png (3.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.