0

I'm using SQL Express 2012, and I'm rather new to it so this website has been a blessing so far! I'm now stuck on a query that I've not found a suitable answer to.

I have a table called Claims Passed. In this I have a column called Client_Name, in this is a list of names, these contain first and second names split by a space (e.g John Smith). I've created two new columns, Client_First_Name and Client_Surname.

What I'm trying to do is get the get the first name in to first name column and the surname into the surname column. I came across something like this but it was only for one row, not all the rows in one go. How can i do this?

Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198
davidn84
  • 19
  • 5
  • 1
    You can fine the answer here: http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns. Which is very complicated for such simple problem. I prefer avoid doing this in SQL. – MinhD Jun 05 '14 at 10:36

2 Answers2

1

This is the basic syntax you need, assuming each name has only one first name:

UPDATE [Claims Passed]
    SET Client_First_Name = SUBSTRING(Client_Name, 1, CHARINDEX(' ', Client_Name) - 1),
    Client_Surname = SUBSTRING(Client_Name,CHARINDEX(' ', Client_Name) + 1, LEN(Client_Name)
kmo
  • 264
  • 1
  • 4
0

The problem on your approach is when a client has more than one surname and one last name this won't work. You could try something like this:

INSERT INTO Client_First_Name VALUES(SELECT Client_Name LIKE '% %' THEN LEFT(Client_Name, Charindex(' ', Client_Name) - 1));

INSERT INTO Client_Surname VALUES(SELECT Client_Name LIKE '% %' THEN RIGHT(Client_Name, Charindex(' ', Reverse(Client_Name)) - 1));
Nikel Weis
  • 724
  • 10
  • 27