By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Source
(firstName varchar(50))
insert into Source(firstName)
values('ANNETTE W * LOW')
select
substring(firstName,1,charIndex('*',firstName) -1) as firstName
, ltrim(substring(firstName, charIndex('*',firstName) + 1
,len(firstName) - (charIndex('*',firstName)))) as lastName
,charIndex('*',firstName) as positionChar
,len(firstName) as lengthString
from Source as S
ALTER TABLE Source
ADD lastName varchar(50) null
UPDATE Source
SET lastName = ltrim(substring(firstName, charIndex('*',firstName) + 1
,len(firstName) - (charIndex('*',firstName))))
,firstName = substring(firstName,1,charIndex('*',firstName) -1)
SELECT * FROM Source
| firstName | lastName | positionChar | lengthString |
|---|---|---|---|
| ANNETTE W | LOW | 11 | 15 |
| firstName | lastName |
|---|---|
| ANNETTE W | LOW |