CODEWITHSUNDEEP
sqlsql-server
DaveWibs
DaveWibs

Reputation: 3

Separating a name out from one column

Here's what I'm working with;

FirstName - Lastname
John Smith

Here's what I'm trying to do;

FirstName - LastName
John        Smith

I'm getting syntax errors and I'm having a bit of a hard time figuring it out;

update [TrainingDB].dbo.Person set
    FirstName, CHARINDEX(' ', FirstName) as firstname
    , substring(FirstName, CHARINDEX(' ', FirstName)+1, len(FirstName)-(CHARINDEX(' ', FirstName)-1)) as LastName

Heres the error I'm getting;

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ','.

Do I need to wrap that second line beginning with set ?

Really appreciate any help/advice.

Upvotes: 0

Views: 69

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 12959

From SQL Server 2016 onwards, you can use JSON functions to split the name into an array and get the values. One more approach of using JSON.

DECLARE @table table(firstName varchar(50), LastName VARCHAR(50))

INSERT INTO @table(firstName)
values('John Smith')

UPDATE ta
SET firstName = json_value(t.NameArray,'$[0]') ,
    lastName =  json_value(t.NameArray,'$[1]') 
from @table as ta
CROSS APPLY ( 
select CONCAT('["',REPLACE(ta.firstName,' ','","'),'"]')
) as t(NameArray)

SELECT * FROM @table

+-----------+----------+
| firstName | LastName |
+-----------+----------+
| John      | Smith    |
+-----------+----------+

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

I would write this as:

UPDATE Person
    SET FirstName = LEFT(FirstName, CHARINDEX(' ', FirstName) - 1),
        LastName = STUFF(FirstName, 1, CHARINDEX(' ', FirstName), '')
    WHERE FirstName LIKE '% %';

This works by:

  • LEFT() takes everything up to but not including the first space in the name.
  • STUFF() removes everything up to and including the first space in the name.
  • The WHERE only does the update if there is a space in the name.

Upvotes: 1

Akina
Akina

Reputation: 42611

The solution for SQL Server (MS SQL):

UPDATE Person
SET Lastname = SUBSTRING(FirstName, CHARINDEX(' ', FirstName) + 1, LEN(FirstName)),
    FirstName = SUBSTRING(FirstName, 1, CHARINDEX(' ', FirstName) - 1);

fiddle

PS. The query will fail if the value does not contain a space char - you may add WHERE CHARINDEX(' ', FirstName) > 0 for troubleshotting.

Upvotes: 1

Related Questions