Split a column into 2 columns

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

re: Split a column into 2 columns

"Prit" <prithwishmukherjee@hotmail.com> wrote in message
news:1105647534.836290.122080@z14g2000cwz.googlegr oups.com...[color=blue]
> 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
>[/color]

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

re: Split a column into 2 columns

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

re: Split a column into 2 columns

On 13 Jan 2005 12:52:27 -0800, Prit wrote:
[color=blue]
>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[/color]

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)

re: Split a column into 2 columns

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?

re: Split a column into 2 columns

On 13 Jan 2005 14:50:44 -0800, Prit wrote:
[color=blue]
>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?[/color]

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)

re: Split a column into 2 columns

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