How to parse a row in SQL into columns (separator-spaces)

Question

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?

score 1 · Answer 1 · edited May 23 '17 at 12:32

1

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/

Split function by comma in SQL Server 2008

edited May 23 '17 at 12:32

Community

1
1

answered Sep 04 '14 at 06:52

Sabin B

293
10
14

score 1 · Answer 2 · answered Sep 04 '14 at 06:57

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

score 1 · Accepted Answer · answered Sep 04 '14 at 07:10

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

Check Demo here..

score 1 · Answer 4 · answered Sep 04 '14 at 07:14

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

Fiddle

How to parse a row in SQL into columns (separator-spaces)

4 Answers4