How can I split a column that contains a persons full name into First Name Surname?-CodePudding

I am using SQL Server and SSMS.

How can I split a column that contains a persons full name into a First Name and Surname column?

For example In the column I have the name 'Jonathon Smith Roberts'. I'd like to split the name so that 'Jonathon' goes in the First Name column and 'Smith Roberts' in the Surname column.

CodePudding user response：

You can accomplish this using Substr

Example -

SELECT SUBSTR(Name, 1, Instr(Name, ' ')) AS FirstName,
       SUBSTR(Name, Instr(Name, ' ')) AS LastName FROM TABLE_NAME;

This would split the Name column into two columns FirstName and LastName using the space between the FirstName and LastName in Name column.

CodePudding user response：

ALTER TABLE Table
ADD FirstName varchar(100);

UPDATE Correspondence SET [FirstName]= SUBSTRING([Response By], 1, CHARINDEX(' ', [Response By]) - 1);      
      
ALTER TABLE Table
ADD Surname varchar(100);

UPDATE Table SET [Surname]= SUBSTRING([Response By], CHARINDEX(' ', [Response By])   1, LEN([Response By]) - CHARINDEX(' ', [Response By]));