无效的号码错误！似乎无法解决它

本文介绍了无效的号码错误！似乎无法解决它的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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