By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t(ID int, Category_ID int, comment varchar(50))
insert into t select 1, 1 , 'abcd'
insert into t select 2, 1 , 'abcd efg'
insert into t select 3, 1 , 'abcd efg hij'
insert into t select 4, 2 , 'onetwo'
insert into t select 5, 2 , 'onetwo three'
insert into t select 6, 3 , 'some'
insert into t select 7, 3 , 'some'
7 rows affected
select *,
trim(Replace(comment, Lag(comment,1,'') over(partition by category_id order by id), '')) Corrected
from t
| ID | Category_ID | comment | Corrected |
|---|---|---|---|
| 1 | 1 | abcd | abcd |
| 2 | 1 | abcd efg | efg |
| 3 | 1 | abcd efg hij | hij |
| 4 | 2 | onetwo | onetwo |
| 5 | 2 | onetwo three | three |
| 6 | 3 | some | some |
| 7 | 3 | some |