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

Split a column into 2 columns

P: n/a
Hi everyone
I guess this should be a simple question for the gurus
I have a Data in a column which is to be places in 2 columns instead of
one. How do i go about doing it in MS SQL server? Could someone please
help me. I could do it in access with an update query but things are a
little different in SQL server so I am a little lost.

Eg.
Name
John?Doe
to be split into
Name LastName
John Doe

Thanks in advance.
Prit

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"Prit" <pr****************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hi everyone
I guess this should be a simple question for the gurus
I have a Data in a column which is to be places in 2 columns instead of
one. How do i go about doing it in MS SQL server? Could someone please
help me. I could do it in access with an update query but things are a
little different in SQL server so I am a little lost.

Eg.
Name
John?Doe
to be split into
Name LastName
John Doe

Thanks in advance.
Prit


You can use string functions:

declare @name varchar(20)
set @name = 'john?doe'

select left(@name, charindex('?', @name)-1), right(@name,
len(@name)-charindex('?', @name))

So something like this?

insert into dbo.Destination (fname, lname)
select left(fullname, charindex('?', fullname)-1), right(fullname,
len(fullname)-charindex('?', fullname))
from dbo.Source

Simon
Jul 23 '05 #2

P: n/a
Simon
Thanks for the reply. It does work with the insert query. When I tried
to use it with an update query it did not quite like it. I wasn't sure
what was going wrong. Sorry I am still trying to find my way around the
SQL server. Does it have a update query equivalent of MS access?
thanks
prit

Jul 23 '05 #3

P: n/a
On 13 Jan 2005 12:52:27 -0800, Prit wrote:
Simon
Thanks for the reply. It does work with the insert query. When I tried
to use it with an update query it did not quite like it. I wasn't sure
what was going wrong. Sorry I am still trying to find my way around the
SQL server. Does it have a update query equivalent of MS access?
thanks
prit


Hi Prit,

This UPDATE should work just as well:

UPDATE MyTable
SET fname = left(fullname, charindex('?', fullname)-1),
lname = right(fullname, len(fullname)-charindex('?', fullname))

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4

P: n/a
Hi Hugo
Thanks for the reply
It gives me the following error

Server: Msg 8114, Level 16, State 5, Procedure Part_Trigger, Line 26
Error converting data type varchar to numeric.

Surprisingly enough both my variables are varchar (50)
Any ideas why this is happening?

Jul 23 '05 #5

P: n/a
On 13 Jan 2005 14:50:44 -0800, Prit wrote:
Hi Hugo
Thanks for the reply
It gives me the following error

Server: Msg 8114, Level 16, State 5, Procedure Part_Trigger, Line 26
Error converting data type varchar to numeric.

Surprisingly enough both my variables are varchar (50)
Any ideas why this is happening?


Hi Prit,

The error message claims to stem from a procedure called "Part_Trigger".
Unless you have a very funny naming scheme, this procedure is actually a
trigger, and it's probably a trigger that fires on updates to the table
you are handling.

I'd suggest you find the code for the trigger and check it for the cause
of this error. Start looking near line 26 (see error message). Post the
trigger's code here, along with the CREATE TABLE statement for the table,
if you can't find the error.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #6

P: n/a
Thanks hugo
Got it to work. There was a problem with the trigger. It was updating a
table which did not have the updated design of this table. I updated
the table and it worked like a charm. :)
Prit

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.