测试数值？ [英] Test for numeric value?

问题描述

我们在我们的临时表中加载的供应商数据相当脏。一列特别捕获数字数据，但40％的时间具有垃圾字符或随机字符串。

我必须创建一个报告来筛选该列中的值范围。所以，我尝试使用替换/翻译的组合，这样

  select replace（translate（upper（str） ，'所有可能的char'），''，''）
从表
  

但它遇到一个没有代码的字符时失败。因此，报告永远不会被自动化。

Javascript具有isNaN（）函数来确定值是否为非法数字（如果为true，则为True，否则为false） 。

如何使用DB2做同样的事情？你有什么想法吗？

提前感谢

解决方案

相当可靠（但有点黑客）的方法是将字符串与其大小写的自身进行比较（数字没有不同的情况）。只要您输入的字符数据只包含拉丁字符，您应该很好：

  SELECT输入，CASE 
 WHEN UPPER（输入）= LOWER（输入）THEN TO_NUMBER（输入）
 ELSE 0 
 END作为输出
 FROM源
  

另一个选择是使用 TRANSLATE 功能：

  SELECT输入，
 CASE 
 WHEN TRANSLATE（CAST（输入为CHAR（10）），'~~~~~~~ ~~~~~~'，'0123456789-。'）='~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ AS num 
 FROM x 
  

The vendor data we load in our staging table is rather dirty. One column in particular captures number data but 40% of the time has garbage characters or random strings.

I have to create a report that filters out value ranges in that column. So, I tried playing with a combination of replace/translate like so

select replace(translate(upper(str),' ','all possible char'),' ','')
from table

but it fails whenever it encounters a char I did not code. Therefore, the report can never be automated.

Javascript has the isNaN() function to determine whether a value is an illegal number (True if it is and false if not).

How can I do the same thing with DB2?? Do you have any idea?

Thanks in advance.

解决方案

A fairly reliable (but somewhat hackish) way is to compare the string to its upper- and lower-case self (numbers don't have different cases). As long as your data that is bringing in characters only includes Latin characters, you should be fine:

SELECT input, CASE
    WHEN UPPER(input) = LOWER(input) THEN TO_NUMBER(input)
    ELSE 0
END AS output
FROM source

Another option would be to use the TRANSLATE function:

SELECT input,
    CASE 
        WHEN TRANSLATE(CAST(input as CHAR(10)), '~~~~~~~~~~~~~', '0123456789-. ') = '~~~~~~~~~~' THEN CAST(input AS DECIMAL(12, 2))
        ELSE 0
    END AS num
FROM x

这篇关于测试数值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！