CODEWITHSUNDEEP
sql-serversubstring
silent_hunter
silent_hunter

Reputation: 2508

Divide personal names in separate columns

I have one table which is called #MyNames.This table in one column contain full name of persons (Name space Surname). In this column we can distinct name from surname with space with them.From left side we have name and after space we have surname.Below you can see table:

enter image description here

So my intention is to divided this column with name MyFullName into two separate columns called ,,FirstName" and ,,LastName''. In order to do this i try with this code:

SELECT LEFT(MyFullName,charindex(' ', MyFullName) - 1) AS FirstName, RIGHT(MyFullName,charindex(' ', MyFullName)-1 )  AS LastName
from #MyNames

Output from this code is not good and I have results like table below :

enter image description here

So can anybody help me how to fix this code and get result correctly and to have fist name in one column and second name in other column?

Upvotes: 0

Views: 143

Answers (2)

seanb
seanb

Reputation: 6685

Obviously the surname clause isn't working correctly.

Instead of using RIGHT(MyFullName,charindex(' ', MyFullName)-1 ) consider SUBSTRING(MyFullName,charindex(' ', MyFullName) + 1, 100) (or whatever field length is).

A better approach (though less easy to read imo) is to replace the first name component with blank '' characters. STUFF(MyFullName,1,charindex(' ', MyFullName),'')

These should both work

SELECT LEFT(MyFullName,charindex(' ', MyFullName)-1) AS FirstName,
       SUBSTRING(MyFullName,charindex(' ', MyFullName)+1, 100) AS LastName
from #MyNames

SELECT LEFT(MyFullName,charindex(' ', MyFullName)-1) AS FirstName,
       STUFF(MyFullName,1,charindex(' ', MyFullName),'') AS LastName
from #MyNames

Upvotes: 1

eavom
eavom

Reputation: 1097

A small correction in your query should fix the problem.

SELECT LEFT(MyFullName,charindex(' ', MyFullName) - 1) AS FirstName
     , RIGHT(MyFullName,len(MyFullName) - charindex(' ', MyFullName))  AS LastName
from #MyNames

Upvotes: 1

Related Questions