By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @V AS VARCHAR(MAX) = 'Price Updated. Previous Value: 20.20 New Value: 30.20 by UsernameA Item Name Updated. Previous Value: XBOX New Value: XBOX2 by UsernameB Item Colour Updated. Previous Value: Black New Value: Silver by UsernameC'
;
select s.value,
left(s.value, charindex(' Previous Value:', s.value)) as name,
left(v.prev, charindex(' New Value:', v.prev)) as prev_value,
left(v.new_val, charindex(' by ', v.new_val)) as new_value,
stuff(v.new_val, 1, charindex(' by ', v.new_val) + 3, '') as user_by
from string_split(replace(@v, ' ', '|'), '|') s cross apply
(values (substring(s.value, charindex(' Previous Value:', s.value) + 17, 1000),
substring(s.value, charindex(' New Value:', s.value) + 12, 1000)
)
) v(prev, new_val)
| value | name | prev_value | new_value | user_by |
|---|---|---|---|---|
| Price Updated. Previous Value: 20.20 New Value: 30.20 by UsernameA | Price Updated. | 20.20 | 30.20 | UsernameA |
| Item Name Updated. Previous Value: XBOX New Value: XBOX2 by UsernameB | Item Name Updated. | XBOX | XBOX2 | UsernameB |
| Item Colour Updated. Previous Value: Black New Value: Silver by UsernameC | Item Colour Updated. | Black | Silver | UsernameC |