SQL Split name into Name and surname

Question

I have table Customers:

Name
David Smith
John David
Jonathan Greg

and i want to transfer it to table Customers2 :

Name      Surname
David     Smith
John      David
Jonathan  Greg

How can i split those names into Name/Surname?

Thank you

What happens if you have an entry like `John Edward Smith`? Or even `Jane Jones Davis`? — Taryn, Jul 22 '14 at 20:55
In addition to @bluefeet's observation (which is a middle name), what about people with 2-part last name like [Dick Van Dyke](http://en.wikipedia.org/wiki/Dick_Van_Dyke)? — alroc, Jul 22 '14 at 20:56
In my case i have only one name and one surname :)) Czech names are much simpler... :D — Nightmaresux, Jul 22 '14 at 20:58

score 2 · Accepted Answer · answered Jul 22 '14 at 21:00

2

Perhaps this but a space better be the deliminator...

Create table customers2 (
SELECT PARSENAME(REPLACE(Name, ' ', '.'), 2) AS [FirstName],
       PARSENAME(REPLACE(Name, ' ', '.'), 1) AS [LastName]
FROM Customers)

answered Jul 22 '14 at 21:00

xQbert

34,733
2
41
62

score 1 · Answer 2 · answered Jul 22 '14 at 21:02

1

Assuming it is strictly name + surname, you can do this

SELECT PARSENAME(REPLACE('David Smith', ' ', '.'), 1) AS NAME,
       PARSENAME(REPLACE('David Smith', ' ', '.'), 1) AS SURNAME

answered Jul 22 '14 at 21:02

C.J.

3,409
8
34
51

1

wouldn't the first 1 need to be a 2? – xQbert Jul 22 '14 at 21:12
1

Yes, i think it should be – Nightmaresux Jul 22 '14 at 21:14

SQL Split name into Name and surname

2 Answers2