Split a column into 2 columns

[Prit]

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

[Simon Hayes]

"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

[Prit]

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

[Hugo Kornelis]

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)

[Prit]

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?

[Hugo Kornelis]

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)

[Prit]

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