我怎么知道oracle 9i中哪些值是数字的 [英] How can I know which values are numeric in oracle 9i

问题描述

我有一个包含varchar的数据库.

I have this database which contains a varchar.

我想知道哪些记录包含数值.我试过REGEXP_COUNT和其他，但是我在9i上运行，我认为这是10g>

I want to know which records holds numeric values. I tried REGEXP_COUNT and other but I'm running on 9i and I think this is for 10g >

我该如何实现?

我尝试过:

 select to_number( my_column ) from my_table 

但是它不起作用，因为并不是所有的数字都是数字.

But it doesn't work, because well not all of them are numeric.

编辑

背景.

此表包含员工ID，所有员工ID均为数字(读取1234或24523或6655)

This table contains employee id's, all of which are numeric ( read 1234 or 24523 or 6655 )

在初始数据库加载中，当未知员工ID而不是使用诸如-1之类的东西时，他们输入了诸如以下的文本:

The in the initial database load, when the employee id was unknown instead of using something like -1 they entered texts like:

NA, N/A, NONE, UNK, UNKNOW, TEST, EXTERNAL, WITHOUT_ID

真正的主要错误是，该列是varchar，而不是应有的编号.

Really the main fault is, that column is varchar and not number as it should.

现在，我想做的是获取ll非数字(不包含雇员id)的记录，但是由于该数据库是9i，所以我无法使用RegExp

Now, what I try to do, is to get ll the records that are not numeric ( that don't contain an employee id ) but since that db is 9i, I could not use RegExp

推荐答案

我设法解决此问题:

select my_column
from my_table
where my_column not like '%1%'
and my_column not like '%2%'
and my_column not like '%3%'
and my_column not like '%4%'
and my_column not like '%5%'
and my_column not like '%6%'
and my_column not like '%7%'
and my_column not like '%8%'
and my_column not like '%9%'
and my_column not like '%0%' 

肮脏，但是可以. ;)

Dirty, but it works. ;)

这篇关于我怎么知道oracle 9i中哪些值是数字的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！