0

I'm working in oracle 11g. I've a table with Number as the datatype. For development purpose we have created a staging table with varchar type. Initially data would be loaded in the staging table. We need find out the records that has only number in that column, since the data might contain the noises. Is there any way to find it.

Cœur
  • 37,241
  • 25
  • 195
  • 267
vvekselva
  • 803
  • 3
  • 17
  • 34
  • just pure positive integers? or negative numbers, floating points (eg 0.124) and exponential notation (eg 1.2e4)? – DazzaL Jan 03 '13 at 12:17
  • possible duplicate of [check if "it's a number" function in Oracle](http://stackoverflow.com/questions/5082176/check-if-its-a-number-function-in-oracle) or this one http://stackoverflow.com/questions/5666986/how-can-i-determine-if-a-string-is-numeric-in-sql – A.B.Cade Jan 03 '13 at 12:39

2 Answers2

3

You can select your data with a regexp_like :

SELECT *
FROM your_table t
WHERE REGEXP_LIKE (t.your_colonne, '^[0-9]+$');
Pilou
  • 1,398
  • 13
  • 24
3

The regexp_like function can be used to determine if a value consists of only digits. Here is an example:

with Your_table(your_column) as(
  select '123456' from dual union all  
  select 'a123452' from dual union all
  select '01456' from dual union all
  select '1j6-d' from dual
)
select your_column
  from your_table
 where regexp_like(your_column, '^[[:digit:]]+$')

Result:

YOUR_COLUMN
--------------
123456
01456

SQL Fiddle Demo

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78