无效的号码错误！似乎无法解决它 [英] Invalid Number Error! Can't seem to get around it

问题描述

Oracle 10g数据库.我有一个名为s_contact的表.该表具有一个名为person_uid的字段.此person_uid字段是varchar2，但包含某些行的有效数字和其他行的无效数字.例如，一行可能具有person_uid的"2-lkjsdf"，另一行可能是1234567890.

Oracle 10g DB. I have a table called s_contact. This table has a field called person_uid. This person_uid field is a varchar2 but contains valid numbers for some rows and in-valid numbers for other rows. For instance, one row might have a person_uid of '2-lkjsdf' and another might be 1234567890.

我只想返回person_uid中具有有效数字的行.我正在尝试的SQL是...

I want to return just the rows with valid numbers in person_uid. The SQL I am trying is...

select person_uid 
from s_contact 
where decode(trim(translate(person_uid, '1234567890', ' ')), null, 'n', 'c') = 'n'

转换将所有数字替换为空格，因此，如果该字段仅包含数字，则修剪将导致null.然后，我使用了一条解码语句来设置一些代码以进行过滤. n =数字，c =字符.

The translate replaces all numbers with spaces so that a trim will result in null if the field only contained numbers. Then I use a decode statement to set a little code to filter on. n=number, c=char.

这在我仅运行预览时似乎有效，但是当我添加...过滤器时出现无效数字"错误.

This seems to work when I run just a preview, but I get an 'invalid number' error when I add a filter of...

and person_uid = 100
-- or
and to_number(person_uid) = 100

我只是不明白发生了什么！它应该过滤掉所有无效数字的记录，而100显然是数字...

I just don't understand what is happening! It should be filtering out all the records that are invalid numbers and 100 is obviously a number...

有任何想法吗?非常感谢！

Any ideas anyone? Greatly Appreciated!

推荐答案

另一种方法是组合谓词:

Another alternative is to combine the predicates:

where case when translate(person_uid, '1234567890', ' ')) is null 
  then to_number(person_uid) end = 100

这篇关于无效的号码错误！似乎无法解决它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！