473,385 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

How to Split a column into two columns and insert

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
4 2122
Rabbit
12,516 Expert Mod 8TB
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
RamSai
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
RamSai
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
I guess we use STUFF() only in case of XML data. In this scenario it may not be necessary.
Jul 31 '13 #5

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

Similar topics

4
by: Garry | last post by:
Hi, I am new to python, hope someone can help me here: I have a MS Access exported .txt file which is tab delimited in total 20 columns, now I need to add another column of zero at the 4th column...
3
by: Stephen Matthews | last post by:
Help please i have a file which im importing, however it is a single column, the data looks like C8517673505 N7062175 C8517673516 N7062178 C8517673527
10
by: Colleyville Alan | last post by:
I am trying to turn a short and fat (63 columns) table into one that is tall and skinny (7 columns). Basically, I am trying to create a "reverse crosstab" using a looping structure in VBA along...
9
by: cavassinif | last post by:
I need to dynamic select a column in which insert a vale based on a parameter value, I have this code, but it throws an incorrect syntax error. How do I dinamically select a column to insert...
1
by: Chicagoboy27 | last post by:
It seems when I use GridView1.Columns.Insert(2, nameColumn); to dynamically insert a column into a grid I loose all items that are bound using <asp:TemplateField HeaderText="E-Mail" >...
0
by: jefftheman812 | last post by:
Hello, I am adding a record into SQL server via VB asp.net. I want to retrieve the ID of the record just added. Below is what i have thus far. It runs, but I cannot figure out how to read or in...
6
by: ljungers | last post by:
Can a Access field let's say called FullName that contains a full name (first last). I I would like to create 2 new columns called FirstName and LastName using the the data in the FullName filed. Can...
1
by: =?Utf-8?B?SGFycnkgS2Vjaw==?= | last post by:
I have a GridView that I dynamically add columns to in code. The way that my code is written, everything works properly if I use GridView.Columns.Add to simply add the columns to the end of the...
6
gcoaster
by: gcoaster | last post by:
Hello Scripts gurus I am Filling a Listbox with combo box values the listbox is showing multiple values what I am trying to do is get these values to INSERT into a table!! this code only...
1
by: Mike1961 | last post by:
Hi all. I have problem with this asp code: strSplitDati = Split(request.Form("dati"), ",") for i = LBound(strSplitDati) to (INT(UBound(strSplitDati)/4)-1)*4 step 4 ...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.