Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t230104a (
  id int not null primary key
, tx text
);

insert into t230104a(id,tx) values
(1, 
'
2011.01.01 依2011薪資調整表作業薪調
2011.01.01 勞保局調整勞保保費

2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907

2014.08.01 勞保局調整勞保保費

'),
(2, '

2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907

2014.08.01 勞保局調整勞保保費');

CREATE TABLE

INSERT 0 2

select *
  from t230104a;

id
tx

1

2011.01.01 依2011薪資調整表作業薪調
2011.01.01 勞保局調整勞保保費

2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907

2014.08.01 勞保局調整勞保保費

2

2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907

2014.08.01 勞保局調整勞保保費

SELECT 2

select id,regexp_split_to_table(tx, '\n|^\r' )
  from t230104a;

id
regexp_split_to_table

1

1
2011.01.01 依2011薪資調整表作業薪調

1
2011.01.01 勞保局調整勞保保費

1

1
2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907

1

1
2014.08.01 勞保局調整勞保保費

1

1

2

2

2
2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907

2

2
2014.08.01 勞保局調整勞保保費

SELECT 14

with t1 as (
select id,regexp_split_to_table(tx, '\n|^\r' ) txt
  from t230104a
)
select id, string_agg(txt, E'\n')
  from t1
 where txt <> ''
 group by id;

id
string_agg

2
2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907
2014.08.01 勞保局調整勞保保費

1
2011.01.01 依2011薪資調整表作業薪調
2011.01.01 勞保局調整勞保保費
2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907
2014.08.01 勞保局調整勞保保費

SELECT 2

with t1 as (
select id,regexp_split_to_table(tx, '\n|^\r' ) txt
  from t230104a
), t2 as (
select id, string_agg(txt, E'\n') as newtxt
  from t1
 where txt <> ''
 group by id
)
update t230104a as a
   set tx = newtxt
  from t2
 where a.id = t2.id;

UPDATE 2

select *
  from t230104a;

id
tx

1
2011.01.01 依2011薪資調整表作業薪調
2011.01.01 勞保局調整勞保保費
2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907
2014.08.01 勞保局調整勞保保費

2
2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907
2014.08.01 勞保局調整勞保保費

SELECT 2

id	tx
1	2011.01.01 依2011薪資調整表作業薪調 2011.01.01 勞保局調整勞保保費 2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907 2014.08.01 勞保局調整勞保保費
2	2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907 2014.08.01 勞保局調整勞保保費

id	regexp_split_to_table
1
1	2011.01.01 依2011薪資調整表作業薪調
1	2011.01.01 勞保局調整勞保保費
1
1	2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907
1
1	2014.08.01 勞保局調整勞保保費
1
1
2
2
2	2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907
2
2	2014.08.01 勞保局調整勞保保費

id	string_agg
2	2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907 2014.08.01 勞保局調整勞保保費
1	2011.01.01 依2011薪資調整表作業薪調 2011.01.01 勞保局調整勞保保費 2013.09.01 物價調薪1%→1階100級$1,044，年功俸$907 2014.08.01 勞保局調整勞保保費