Calculate string difference between consecutive and categorized rows

Question

Unfortunately, I accidentally inserted all my comments concatenated every time in my table. I would like to fix this in my database like in the example:

My table is:

ID	Category_ID	comment
1	1	abcd
2	1	abcd efg
3	1	abcd efg hij
4	2	onetwo
5	2	onetwo three
6	3	some
7	3	some

It should be as follows:

ID	Category_ID	comment
1	1	abcd
2	1	efg
3	1	hij
4	2	onetwo
5	2	three
6	3	some
7	3

I appreciate your help

Answer 1

You can utlise lag here:

select *, 
  trim(Replace(comment, Lag(comment,1,'') 
    over(partition by category_id order by id), '')) Corrected
from t;

Demo Fiddle

After checking the results are as expected you can then use an updatable CTE to correct the data:

with fixy as (
  select *, 
    trim(Replace(comment, Lag(comment,1,'') 
     over(partition by category_id order by id), '')) Corrected
  from t
)
update fixy set comment = corrected;