Splitting Full name data to First, Last

[Yamahab]

I'd like to split a full name field on a space into first name last and update my database, I would prefer to stick to TSQL/SQL if it can be done on SQL Server 2005, any ideas?

table actors

actor_id int Unchecked
actor_name varchar(50) Checked
rating smallint Checked
actor_last_name varchar(50) Checked

DECLARE
@position int,
@fullname varchar(50),
@lastname varchar(50)
DECLARE
Names CURSOR FOR
SELECT actor_name, actor_last_name
FROM actors;
OPEN Names;
FETCH NEXT FROM Names INTO @fullname, @lastname;
WHILE @@FETCH_STATUS = 0
BEGIN
set @position=CHARINDEX(" ",@fullname) -- gives Invalid Column name
if(@position>0)
begin
select COUNT(*) from ratings -just needed something here to compile
end
FETCH NEXT FROM Names
END
CLOSE Names
DEALLOCATE Names

SELECT actor_name, actor_last_name
FROM actors;
GO

[RobStocks]

I'd like to split a full name field on a space into first name last and update my database, I would prefer to stick to TSQL/SQL if it can be done on SQL Server 2005, any ideas?

table actors

actor_id int Unchecked
actor_name varchar(50) Checked
rating smallint Checked
actor_last_name varchar(50) Checked

DECLARE
@position int,
@fullname varchar(50),
@lastname varchar(50)
DECLARE
Names CURSOR FOR
SELECT actor_name, actor_last_name
FROM actors;
OPEN Names;
FETCH NEXT FROM Names INTO @fullname, @lastname;
WHILE @@FETCH_STATUS = 0
BEGIN
set @position=CHARINDEX(" ",@fullname) -- gives Invalid Column name
if(@position>0)
begin
select COUNT(*) from ratings -just needed something here to compile
end
FETCH NEXT FROM Names
END
CLOSE Names
DEALLOCATE Names

SELECT actor_name, actor_last_name
FROM actors;
GO

Hi Yamahab,
It appears that you are already on the right track. The invalid column name is due to the use of double quotes rather than single quotes - something I do often when writing SQL.
e.g.
set @position=CHARINDEX(' ',@fullname)
should resolve the error

[Yamahab]

Thank you, I guess I'm used to php where I can freely switch.

[MCP]

I think you're better off avoiding cursors where possible! It's much slower than a straight up statement, such as:

select name,
   substring(name,1,charindex(' ', actor_name)-1) FirstName,
   substring(name,charindex(' ', actor_name)+1,len(actor_name)) LastName
from actors

to do an update, you could do:

update actors
  set first_name = substring(name,1,charindex(' ', actor_name)-1),
      last_name = substring(name,charindex(' ', actor_name)+1,len(actor_name))