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

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

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

to do an update, you could do: