如何过滤掉 Teradata 文本字段中的非数字值？

Question

我有一个 Teradata 表，其中包含大约 1000 万条记录，该表将数字 id 字段存储为 varchar。我需要将该字段中的值传输到另一个表中的 bigint 列，但我不能简单地说cast(id_field as bigint)，因为我收到无效字符错误。查看这些值，我发现字符串中的任何位置都可能有一个字符，所以假设字符串是 varchar(18) 我可以像这样过滤掉无效行：

     where substr(id_field,1,1) not in (/*big,ugly array of non-numeric chars*/)
     and substr(id_field,2,1) not in (/*big,ugly array of non-numeric chars*/)

etc, etc... 

然后强制转换就可以了，但这是不可行的从长远来看。它很慢，并且如果字符串有 18 个可能的字符，则会导致查询不可读。如何过滤掉在此字段中具有值且不会转换为 bigint 的行，而不单独检查非数字字符数组中的每个字符？

示例值是

   123abc464
   a2.3v65
   a_356087
   ........
   000000000
   BOB KNIGHT
   1235468099

不遵循特定模式的值，我只需要过滤掉包含任何非数字数据的值。 123456789 可以，但 123.abc_c3865 不行……

Answer 1

从TD14 Teradata开始添加了一些功能，现在有多种方法，例如：

WHERE RTRIM(col, '0123456789') = ''

但最简单的方法是TO_NUMBER，对于坏数据返回NULL：

TO_NUMBER(col)

Answer 2

我曾经管理过的最好的方法是：

where char2hexint(upper(id_field)) = char2hexint(lower(id_field))

由于大写字符为小写字符提供了不同的十六进制值，这将确保您没有字母字符，但仍然会留下下划线、冒号等。如果这不能满足您的要求，您可能需要编写 UDF。

Answer 3

我们是否还可以尝试将字段中的值除以某个整数“如果除以则必须是一个数字，如果不是并抛出一些错误，则必须有一些字符......”猜测这会很快，就像数学一样涉及...

Answer 4

我在尝试从街道地址门牌号中排除字母字符时遇到了同样的问题。如果您不介意将所有数字连接在一起，则以下内容将有效......
它检查字符串的上部是否等于字符串的下部，如果是，则为数字，否则为空。

select cast(case when upper(substring('12E'from 1 for 1)) = lower(substring('12E'from 1 for 1)) then substring('12E'from 1 for 1) else null end ||
             case when upper(substring('12E'from 2 for 1)) = lower(substring('12E'from 2 for 1)) then substring('12E'from 2 for 1) else null end ||
             case when upper(substring('12E'from 3 for 1)) = lower(substring('12E'from 3 for 1)) then substring('12E'from 3 for 1) else null end ||
             case when upper(substring('12E'from 4 for 1)) = lower(substring('12E'from 4 for 1)) then substring('12E'from 4 for 1) else null end ||
             case when upper(substring('12E'from 5 for 1)) = lower(substring('12E'from 5 for 1)) then substring('12E'from 5 for 1) else null end ||
             case when upper(substring('12E'from 2 for 1)) = lower(substring('12E'from 2 for 1)) then substring('12E'from 2 for 1) else null end
             as integer) 

Answer 5

尝试使用此代码段

WHERE id_Field NOT LIKE '%[^0-9]%'

Answer 6

我发现 lins314159 答案对于解决类似问题非常有帮助。它可能是一个旧线程，但就其价值而言，我使用了：

char2hexint(upper(id_field)) = char2hexint(lower(id_field)) AND substr(id_field,1,1) IN ('1' to '9 ')

成功将剩余的 VARCHAR 结果转换为 INT

Answer 7

SELECT customer_id
FROM t
WHERE UPPER(customer_id)(CASESPECIFIC) <>
      LOWER(customer_id)(CASESPECIFIC);

这对于检查数字字段中的值是否为非数字非常有效。

Answer 8

SELECT id_field
WHERE oTranslate(id_field, '0123456789','')<>'';

这对我来说效果很好！它显示任何包含非数字值的 id_field