I need to parse row (with spaces) into two different columns in SQL. The rows look like that:
FullName
John Mayer
Rajon Jennings
Donna Fraye
Michael Moore
So the expected result is :
Name
John
Rajon
Donna
Michael
Surname
Mayer
Jennings
Fraye
Moore
How can i do that in SQL?
If you have a requirement like First Name should be string before first space and rest everything what follows should go as Last name , you can update two columns as:
Update T
Set T.FirstName = Left(ltrim(rtrim(FullName)), CHARINDEX(' ',ltrim(rtrim(FullName))))
,T.LastName = Right(ltrim(rtrim(FullName)),len(FullName)- CHARINDEX(' ',ltrim(rtrim(FullName))))
From Test T
You can use a split function.This function must be created. Here are some examples :
http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql/
If there is only ever 1 space / 2 name and you don't have to cater for names like Vincent van Gough, then a combination of substring and charindex should do.
select substring(fullname, 1, charindex(' ', fullname)) as name,
substring(fullname, charindex(' ', fullname)+1, len(fullname)-charindex(' ', fullname)) as surname
from tbl
This does not cover people with the same first and last name (ie. john john) but will work for rest. Not recommended but is a different solution.
select left(fullname, charindex(' ',fullname)) Name,
replace(fullname,left(fullname, charindex(' ',fullname)),'') Surname
from names
