-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
create table products(id integer primary key, artist varchar(32), firstname varchar(32), lastname varchar(32))
go

insert into products values (1,'fn en',NULL,NULL)
go

insert into products values (2,'en, fn',NULL,NULL)
go

select * from products
go

UPDATE products
SET firstname = CASE WHEN CHARINDEX(',',artist)=0 THEN LEFT(artist, CHARINDEX(' ',artist) - 1) ELSE RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist)) END,
    lastname = CASE WHEN CHARINDEX(',',artist)=0 THEN RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist)) ELSE LEFT(artist, CHARINDEX(',',artist) - 1) END
go

select * from products
go
