js实现只能解决html上的显示,但是导出pdf和Excel是不行的,所以最好是在etl的时候就转换好,也可以在模型中处理,但是效率就比较差,我在Oracle中写了下面的SQL,可以实现转换--使用的oracle主要函数
--instr用于搜索
--length取长度
--substr截取字符串
--replace替换
with rmbje as (s...
显示全部js实现只能解决html上的显示,但是导出pdf和Excel是不行的,所以最好是在etl的时候就转换好,也可以在模型中处理,但是效率就比较差,我在Oracle中写了下面的SQL,可以实现转换
--使用的oracle主要函数
--instr用于搜索
--length取长度
--substr截取字符串
--replace替换
with rmbje as (select to_char(字段名) as rmb from 表名),
sburmb as (select rmb,instr(rmb,'.') as xsd,case instr(rmb,'.') when 0 then rmb else substr(rmb,1,instr(rmb,'.')-1) end as zsw,case instr(rmb,'.') when 0 then '' else substr(rmb,instr(rmb,'.')+1,2) end as xsw from rmbje),
zhrmb as (select a.*,length(a.zsw) as zswws from sburmb a),
jqrmb as (select rmb,b.zswws,substr(b.zsw,1,1) w1,substr(b.zsw,2,1) w2,substr(b.zsw,3,1) w3,substr(b.zsw,4,1) w4,substr(b.zsw,5,1) w5,substr(b.zsw,6,1) w6,substr(b.zsw,7,1) w7,
substr(b.zsw,8,1) w8,substr(b.zsw,9,1) w9,substr(b.zsw,10,1) w10,substr(b.zsw,11,1) w11,substr(b.zsw,12,1) w12,substr(b.xsw,1,1) x1,substr(b.xsw,2,1) x2 from zhrmb b),
zh1 as (select a.*,
case w1 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z1,
case w2 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z2,
case w3 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z3,
case w4 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z4,
case w5 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z5,
case w6 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z6,
case w7 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z7,
case w8 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z8,
case w9 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z9,
case w10 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z10,
case w11 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z11,
case w12 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end z12,
case x1 when '0' then '零角' when '1' then '壹角' when '2' then '贰角' when '3' then '叁角' when '4' then '肆角' when '5' then '伍角' when '6' then '陆角' when '7' then '柒角' when '8' then '捌角' when '9' then '玖角' end xs1,
case x2 when '0' then '零分' when '1' then '壹分' when '2' then '贰分' when '3' then '叁分' when '4' then '肆分' when '5' then '伍分' when '6' then '陆分' when '7' then '柒分' when '8' then '捌分' when '9' then '玖分' end xs2
from jqrmb a),
zh2 as (select
rmb,
case zswws
when 1 then z1||'圆'
when 2 then z1||(case when w1='0' then '' else '拾' end)||z2||'圆'
when 3 then z1||(case when w1='0' then '' else '佰' end)||z2||(case when w2='0' then '' else '拾' end)||z3||'圆'
when 4 then z1||(case when w1='0' then '' else '仟' end)||z2||(case when w2='0' then '' else '佰' end)||z3||(case when w3='0' then '' else '拾' end)||z4||'圆'
when 5 then z1||'萬'||z2||(case when w2='0' then '' else '仟' end)||z3||(case when w3='0' then '' else '佰' end)||z4||(case when w4='0' then '' else '拾' end)||z5||'圆'
when 6 then z1||(case when w1='0' then '' else '拾' end)||z2||'萬'||z3||(case when w3='0' then '' else '仟' end)||z4||(case when w4='0' then '' else '佰' end)||z5||(case when w5='0' then '' else '拾' end)||z6||'圆'
when 7 then z1||(case when w1='0' then '' else '佰' end)||z2||(case when w2='0' then '' else '拾' end)||z3||'萬'||z4||(case when w4='0' then '' else '仟' end)||z5||(case when w5='0' then '' else '佰' end)||z6||(case when w6='0' then '' else '拾' end)||z7||'圆'
when 8 then z1||(case when w1='0' then '' else '仟' end)||z2||(case when w2='0' then '' else '佰' end)||z3||(case when w3='0' then '' else '拾' end)||z4||'萬'||z5||(case when w5='0' then '' else '仟' end)||z6||(case when w6='0' then '' else '佰' end)||z7||(case when w7='0' then '' else '拾' end)||z8||'圆'
when 9 then z1||'亿'||z2||(case when w2='0' then '' else '仟' end)||z3||(case when w3='0' then '' else '佰' end)||z4||(case when w4='0' then '' else '拾' end)||z5||'萬'||z6||(case when w6='0' then '' else '仟' end)||z7||(case when w7='0' then '' else '佰' end)||z8||(case when w8='0' then '' else '拾' end)||z9||'圆'
when 10 then z1||(case when w1='0' then '' else '拾' end)||z2||'亿'||z3||(case when w3='0' then '' else '仟' end)||z4||(case when w4='0' then '' else '佰' end)||z5||(case when w5='0' then '' else '拾' end)||z6||'萬'||z7||(case when w7='0' then '' else '仟' end)||z8||(case when w8='0' then '' else '佰' end)||z9||(case when w9='0' then '' else '拾' end)||z10||'圆'
when 11 then z1||(case when w1='0' then '' else '佰' end)||z2||(case when w2='0' then '' else '拾' end)||z3||'亿'||z4||(case when w4='0' then '' else '仟' end)||z5||(case when w5='0' then '' else '佰' end)||z6||(case when w6='0' then '' else '拾' end)||z7||'萬'||z8||(case when w8='0' then '' else '仟' end)||z9||(case when w9='0' then '' else '佰' end)||z10||(case when w10='0' then '' else '拾' end)||z11||'圆'
when 12 then z1||(case when w1='0' then '' else '仟' end)||z2||(case when w2='0' then '' else '佰' end)||z3||(case when w3='0' then '' else '拾' end)||z4||'亿'||z5||(case when w5='0' then '' else '仟' end)||z6||(case when w6='0' then '' else '佰' end)||z7||(case when w7='0' then '' else '拾' end)||z8||'萬'||z9||(case when w9='0' then '' else '仟' end)||z10||(case when w10='0' then '' else '佰' end)||z11||(case when w11='0' then '' else '拾' end)||z12||'圆'
end zs,
xs1||xs2 xs
from zh1),
zh3 as (select rmb,replace(replace(replace(replace(replace(replace(zs,'零零','零'),'零零','零'),'零亿','亿'),'零萬','萬'),'零圆','圆'),'亿萬','亿')||replace(replace(replace(xs,'零分','零'),'零角','零'),'零零','') rmbdx from zh2),
zh4 as (select rmb,case when rmbdx like '%圆' then rmbdx||'整' when rmbdx like '零%' then substr(rmbdx,2,length(rmbdx)-1) else rmbdx end dxrmb from zh3)
select * from zh4
收起