如题,比如字符“A B C”中间包含不定长的空格,想将中间的不定长空格替换为一个空格。如:“A B C",用SQL语句实现。有什么好方法么?
xquery里面有正则表达式,用正则替换做比较方便。
https://www.ibm.com/developerworks/jp/data/library/db2/j_d-regularexpression/
可以参考下这里的函数
如下方法供参考:(首先已经空格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
收起