declare @t table (ORDERID int, _NAME varchar(30), CUSTOMER_SURNAME varchar(20))
insert @t (ORDERID, _NAME)

select 1, 'MR/MRS BADACH' union
select 2, 'MR ROCHFORD & MISS HENDERSON' union
select 3, 'MR/MRS PATERSON' union
select 4, 'MISS JONES & MISS QUINLIVAN' union
select 5, 'MR/MRS BRADLEY' union
select 6, 'MR/MRS CLEECE' union
select 7, 'MR/MRS FOWLER' union
select 8, 'D JONES' union
select 9, 'K COUCHLIN' union
select 10, 'CLARKE' union
select 11, 'L WRIGHT'

update @t
set customer_surname = 
case
	when charindex(' ', _name) > 0 then reverse(substring(reverse(_name), 0, charindex(' ', reverse(_name)))) 
	else _name
end
from @t

select * from @t

update @t
set customer_surname = REVERSE(STUFF(REVERSE(_NAME),CHARINDEX(' ',REVERSE(_NAME)),LEN(_NAME),''))

quote:

---

Originally posted by Skorch

The above still doesn't account for case #10 where you get a NULL

Some days you're the dog, and some days you're the fire hydrant.

---

update @t
set customer_surname = REVERSE(STUFF(REVERSE(_NAME),CHARINDEX(' ',REVERSE(_NAME) + ' '),LEN(_NAME),''))

update @t
set customer_surname = 
case
	when charindex(' ', _name) > 0 then reverse(substring(reverse(_name), 0, charindex(' ', reverse(_name)))) 
	else _name
end
from @t

quote:

---

Originally posted by rajendrarama

Thank you. It has updated the table. For any new row insertion in the future,
how can i automatically update the surname column?

---

ALTER TABLE tablename ADD customer_surname AS case
	when charindex(' ', _name) > 0 then reverse(substring(reverse(_name), 0, charindex(' ', reverse(_name)))) 
	else _name
end

quote:

---

Originally posted by rajendrarama

If the user has entered a space after the name, then it is not updating the customer_surname. could you please help with it.

---