oracle数据库，如何判断某列是否为有效数字？

likeyouli

数字的可能情况：（首先看看该列是不是为数值类型，如果不是，只能用regexp_substr(列名,'正则表达式',从匹配的第几个开始截取,提取几个）提取
null
0
0.090000
.09
123
123.45
-123.45
+123.45
用to_number或cast实测，select to_number(fy) from ceshi   或者 select t.*,cast(fy as number) fy_zhuan from ceshi t：①当单元格内容啥都没有时，不报错；②当单元格内容为null时报错；③当单元格内容的数字前有±号时（+3.45、-123.45）时不报错，但为±3.45时报错；④当单元格内容为.99时不报错，会转为0.99；⑤当单元格内容为00000时，会转为0；⑥......    所以，这样用比较好：select to_number(nvl2(fy,fy,0)) from ceshi ，当为null时返回0，当不为有效数值时还能报错：ORA-01722: invalid number（无效数字）。

方法一：如果您使用的是Oracle 12c或更高版本，可以使用内置的VALIDATE_CONVERSION函数
select shfzhm,xm,zfy,CASE WHEN VALIDATE_CONVERSION(zfy AS NUMBER) = 1 THEN zfy else 99999999999 end as zfy_zhuan,
jjzf,CASE WHEN VALIDATE_CONVERSION(jjzf AS NUMBER) = 1 THEN jjzf else 99999999999 end as jjzf_zhuan
from yb_jszd_ws  order by jjzf_zhuan  desc
SELECT t.*,
  CASE  WHEN VALIDATE_CONVERSION(省里死亡时间二 AS DATE, 'yyyy-mm-dd') = 1 THEN TO_DATE(省里死亡时间二, 'yyyy-mm-dd') ELSE NULL END AS converted_date
FROM zhangrui_duibi t order by converted_date desc


方法二：使用正则表达式
select shfzhm,xm,zfy,case when REGEXP_LIKE(zfy, '^[-+]?(\d+(\.\d*)?|\.\d+)$') then zfy else 999999999999  end as zfy_zhuan,
jjzf,case when REGEXP_LIKE(jjzf, '^[-+]?(\d+(\.\d*)?|\.\d+)$') then jjzf else 999999999999  end as jjzf_zhuan
from yb_jszd_ws  order by jjzf_zhuan  desc


求和时，先转为数值再求和：
select shfzhm,xm,(jsrq-chsrq)/365 as 结算时年龄, sum(case when zfy is null then 0 else zfy end) as 总金额,
sum(case when REGEXP_LIKE(zfy, '^-?\d+(\.\d+)?$') then zfy else 0 end)-sum(case when jjzf is null then 0 else jjzf end) 个人负担金额,
sum(case when jjzf is null then 0 else jjzf end)/sum(case when zfy is null then 0 else zfy end) 比例,to_char(cyrq,'yyyy'),yltclx,jsfs,rqlb from yb_jszd_ws
where zfy >0 and yltclx not like '生育%' and main_dis_name not like '%分娩%' and jsrq-chsrq >=100*365
group by shfzhm,xm,to_char(cyrq,'yyyy'),yltclx,jsfs,rqlb,jsrq,chsrq order by 总金额 desc, 比例 desc

likeyouli

占楼备用





占

likeyouli

测试是不是数值的几种方式，其中前2种可以是任意数值，后边3种仅能测试null值：

select count(*) from
(select jjzf,CASE WHEN VALIDATE_CONVERSION(jjzf AS NUMBER) = 1 THEN jjzf else 99999999999 end as jjzf_zhuan from yb_jszd_ws) where jjzf_zhuan like '99999999999'
--用validate_conversion函数，效率很高，仅适用于oracle12c以上版本

select count(*)from
(select case when REGEXP_LIKE(jjzf, '^[-+]?(\d+(\.\d*)?|\.\d+)$') then jjzf else 999999999999  end as jjzf_zhuan from yb_jszd_ws) where jjzf_zhuan like '999999999999'
--用正则表达式测试是不是数值，最灵活，但效率也最低

--下边3个仅能测试null
select count(*) from
(select jjzf,nvl(jjzf,999999999999) as jjzf_zhuan from yb_jszd_ws) where jjzf_zhuan like '999999999999'
--nul转换null值为一个实际值，也就是说只转换null，不转换其他的

select count(*) from
(select jjzf,nvl2(jjzf,jjzf,999999999999) as jjzf_zhuan from yb_jszd_ws) where jjzf_zhuan like '999999999999'
--nvl2只能是3个参数，如果第一个参数为空，返回第3个参数；如果第一个参数不为空，返回第2个参数

select count(*) from
(select jjzf,coalesce(jjzf,jjzf,999999999999) as jjzf_zhuan from yb_jszd_ws) where jjzf_zhuan like '999999999999'
--coalesce可以是多个参数，返回参数中第一个非null值，如果前边的都是null，则返回最后一个值,这里用了3个，其实完全可以是2个参数







.

martin313

看不懂
要么问AI