如何用SQL将字段中间不定长的空格替换为一个空格

如下方法供参考：（首先已经空格replacec成了‘0’）

1.首先将字符按照一个一个字符做成一列数据，

drop table tmp_table_01;

create table tmp_table_01

(

  num1 varchar(2),

  num  integer,

  va   varchar(255)

) data capture none

   in tbs_dw4 partitioning key(num) using hashing;

insert into tmp_table_01

with t1(num1,num,va) as

(

select substr(va,num,1) as num1,num,va from (values (\'A000000CD00E0F000G\',1)) a(va,num)

union all

select substr(va,num+1,1),num+1,va  from t1 where t1.num+1<=length(va)

) select num1,num,va from t1;

------------------------------------------------------------------------------------------

2.自关联已经成为一列数据的字符串，做如下判断：

如果该字符串前一个字符为0且本身为0将自己换成‘’，否则保持自己不变

3.将做了判断的字符串在汇成一个字符串即可。DB2是有一个函数可以完成此项操作的：listagg()

select replace(listagg(num2,\'\') within group(order by num),\'0\',\' \') from

(

select case when a.num1=\'0\' and b.num1=\'0\' then \'\' else a.num1 end as num2,a.num

from tmp_table_01 a

left join tmp_table_01 b on a.num-1=b.num

)  a