By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,462 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,462 IT Pros & Developers. It's quick & easy.

How to Split a column into two columns and insert

P: 3
I have
Expand|Select|Wrap|Line Numbers
  1.    Guest_name
  2.   Shane Lindsay
  3.   Jose 
need to split GUEST_Name into two columns like

Expand|Select|Wrap|Line Numbers
  1. GUEST_First_Name  GUEST_Last_Name
  2.   Shane             Lindsay
  3.   Jose 
I run this query ,but which is giving error

Expand|Select|Wrap|Line Numbers
  1. UPDATE csep_invitee_guest SET  GUEST_FIRST_NAME = left(guest_name,charindex('', guest_name)-1),
  2. 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
May 8 '13 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
May 8 '13 #2

P: 3
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
May 8 '13 #3

P: 3
Hi Rabbit

thanks for your help which is working fine.when i run this
Expand|Select|Wrap|Line Numbers
  1. UPDATE csep_invitee_guest SET  GUEST_FIRST_NAME = LEFT(guest_name, CHARINDEX(' ', guest_name + ' ') -1),
  2. GUEST_LAST_NAME = STUFF(guest_name, 1, Len(guest_name) +1- CHARINDEX(' ',Reverse(guest_name)), '') WHERE invitee_guest_id=9905
May 8 '13 #4

P: 2
I guess we use STUFF() only in case of XML data. In this scenario it may not be necessary.
Jul 31 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.