How to Split a column into two columns and insert

[RamSai]

I have

   Guest_name
  Shane Lindsay
  Jose

need to split GUEST_Name into two columns like

GUEST_First_Name  GUEST_Last_Name
  Shane             Lindsay
  Jose

I run this query ,but which is giving error

UPDATE csep_invitee_guest SET  GUEST_FIRST_NAME = left(guest_name,charindex('', guest_name)-1),
GUEST_LAST_NAME = right(guest_name, len(guest_name)-charindex('', guest_name))

Error is
Error: Invalid length parameter passed to the SUBSTRING function.
SQLState: S0005
ErrorCode: 536

[Rabbit]

Please use code tags when posting code or formatted data.

In your charindex, you're looking for a blank string. I'm pretty sure you're looking for a space. Also, in the situations where it's only a first name, there will be no space to find so you'll want to append a space to the end.

[RamSai]

Thanks for your reply,i understood your explanation but am unable to implement.If you don't mind can you send me the Query.

thanks in advance

[RamSai]

Hi Rabbit

thanks for your help which is working fine.when i run this

UPDATE csep_invitee_guest SET  GUEST_FIRST_NAME = LEFT(guest_name, CHARINDEX(' ', guest_name + ' ') -1),
GUEST_LAST_NAME = STUFF(guest_name, 1, Len(guest_name) +1- CHARINDEX(' ',Reverse(guest_name)), '') WHERE invitee_guest_id=9905

[saichaitanya55]

I guess we use STUFF() only in case of XML data. In this scenario it may not be necessary.