Skip to Main Content
  • Questions
  • Need to select fields having only numeric data

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Priti .

Asked: June 26, 2009 - 9:04 am UTC

Last updated: November 01, 2013 - 8:30 pm UTC

Version: 9.2.0

Viewed 100K+ times! This question is

You Asked

Hi,

I have a requirement to select only those fields which have numeric value and do not contain any characters

SQL> desc priti
Name Null? Type
----------------------------------------- -------- ----------------------------
VAL1 VARCHAR2(5)

SQL> select * from priti;

VAL1
-----
1234
abcd
1sf3

The query should return only 1234

How can we do this?

Regards,
Priti

and Tom said...

I really think that in our profession, where an attention to detail is mandatory, that everyone should be able to read

http://asktom.oracle.com/tkyte/question_policy.htm

which they have to when asking a question and check a box on that page stating "I've read this and agree to it".... And follow the rather simple directions.

Read #3 and ask yourself Priti "did I do that, or did I do what I was told to NOT DO".

You did exactly what you were told not to do.


sigh.

ops$tkyte%ORA10GR2> select replace( translate( '1234', '0123456789', '0000000000' ), '0', '' ) from dual;

R
-


ops$tkyte%ORA10GR2> select replace( translate( '12a4', '0123456789', '0000000000' ), '0', '' ) from dual;

R
-
a




you can use replace(translate()) to make every val1 become "null" if it only contains digits 0-9. Use the replace(translate()) in your where clause to retrieve just the rows you want.

Rating

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Can we not also use ascii function?

Ravi, June 26, 2009 - 1:42 pm UTC

Can we not also use ascii function?
Thanks!
Ravi.

Tom Kyte
June 26, 2009 - 2:30 pm UTC

how?

the ascii function takes as input a character and returns the ascii code of it.

how would you use that function to verify that a string of characters consisted only of the digits 0-9?

two options

Mike Kutz, June 26, 2009 - 5:16 pm UTC

OPTION 1 - upgrade DB
In 10g+, you can use regular expressions.
Keep in mind, this will force a full table scan. (i believe)

-- this row will show 'the error of this way'
insert into priti values ('1234 ');

select val1 from priti
where regexp_like(val1,'^[[:digit:]]+$');

val1
-----
1234
* note '1234 ' is missing

OPTION 2 - fix table
IMHO - if you need an 'integer only' column, then use an 'integer only' column.
step 1) fix table
alter table add (val1_num integer);
create index priti_val1num_ix on priti(val1_num);

step 2) make function
* untested and incomplete code *
Since I don't know how to do it in PL/SQL, I'll use Java:
public class fixme {
public static java.lang.String my_parseInt( String input ) {
if( input == null ) return null;
try {
return new Integer(input).toString();
} catch (Exception e) {
// untested for String
// Float & Double will return 0
// (or was that NaN? But not null)
return null;
}
}
}

// you'll need to search the internet on what to do
// next after you compile it

step 3) make a trigger to 'insert/update' the val1_num field
* untested, i may have statement wrong *

create trigger priti_trig_bi
for each row before insert update
begin
:new.val1_num = my_parseInt( :new.val1 );
end;

step 4) update the table data for older pre-trigger data

update priti set val1_num=my_parseInt( val1 )
where val1_num is null;

step 5) now use this simple select

select val1 from priti where val1_num is not null;
* since we can use an index, it shouldn't have to do a full table scan (unverified statement)

OPTION 3 - combine both options
effectively, you will use the regexp_?? functions, instead of the java function, in the trigger to test if it is a number prior to assigning val1_num

After that, step 4 then becomes:
update priti set val1_num=val1
where val1_num is null and regexp_like( ... );

RESULT OF FIXING TABLE
select '''' || val1 || '''' , val1_num from priti;

''''||V VAL1_NUM
------- ----------
'1234' 1234
'abcd'
'12f4'
'1234 ' 1234


Tom Kyte
June 26, 2009 - 6:00 pm UTC

... Keep in mind, this will force a full table scan. (i believe)....

you can index functions, we could index the replace(translate()) or the regular expression

however, if you can do it without regular expressions - do it without them. regular expressions consume CPU hugely. They are neat, but come at a high high price.


all of what you wrote seems so much more complex than:

where replace(translate(column,'...','...'),'0','') is null



What about values like '32.45'?

César, June 27, 2009 - 10:22 am UTC

create table data
(
info varchar2(5)
)
/

insert into data
select 
  decode(rownum,
    1, '12345',
    2, '.8293',
    3, '12i38',
    4, '345.8',
    5, 'jjjjj',
    6, 'eeeee',
    7, 'e',
    8, '1f.93',
    9, '1.',
    10, '2.2.',
    11, '3...4',
    12, 'e.e',
    13, '.e',
    14, '98',
    15, '',
    16, 'e.',
    17, '0.0',
    18, '23.45',
    19, '.....',
    20, '3....')
from all_objects
where rownum < 21
/

SQL> select * from data;

INFO
-----
12345
.8293
12i38
345.8
jjjjj
eeeee
e
1f.93
1.
2.2.
3...4
e.e
.e
98

e.
0.0
23.45
.....
3....


SQL> select * from data
  2  where info is not null
  3  and
  4  (
  5  translate(info, '.01234567890', '.') is null
  6  or translate(replace(translate(replace(info, 'e', 'a'), '.0123456789', '.0000000000'), '.0', 'e'), '.0', '.') = 'e'
  7  or translate(replace(translate(replace(info, 'e', 'a'), '.0123456789', '.0000000000'), '0.', 'e'), '.0', '.') = 'e'
  8  )
  9  /

INFO
-----
12345
.8293
345.8
1.
98
0.0
23.45



Or if values .8293 and 1. are ivalid for you:



SQL> select * from data
  2  where info is not null
  3  and
  4  (
  5  translate(info, '.01234567890', '.') is null
  6  or translate(replace(translate(replace(info, 'e', 'a'), '.0123456789', '.0000000000'), '0.0', 'e'), '.0', '.') = 'e'
  7  )
  8  /

INFO
-----
12345
345.8
98
0.0
23.45


Tom Kyte
July 06, 2009 - 5:03 pm UTC

given the specification I was given, 32.45 would not be an option, so I don't see any reason to consider it.


and then it begs the question is 1.24e2 a valid number, etc.

I only answer what I see these days, if people are not precise in their question - they get what they get.


but thanks for the followup :)

A reader, June 27, 2009 - 10:39 am UTC

Priti , 

Assuming you are on 10g ,  Regular Expression can be used .

Beware , regular expression are cpu intensive as Tom had indicated in few posts . 

You may want to benchmark the query timings . 


SQL> Select * from  v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> drop table t1;

Table dropped.

SQL>
SQL> Create table t1 ( col1 varchar2(10)) ;

Table created.

SQL>
SQL> insert into t1 values('ZAHIR') ;

1 row created.

SQL> insert into t1 values('08831') ;

1 row created.

SQL> insert into t1 values('07066') ;

1 row created.

SQL> insert into t1 values('48331') ;

1 row created.

SQL> insert into t1 values('DEEN') ;

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SELECT col1
  2  FROM t1
  3  WHERE REGEXP_LIKE(col1, '[[:digit:]]');

COL1
----------
08831
07066
48331

SQL>

Tom Kyte
July 06, 2009 - 5:04 pm UTC

rule:

if you can do it without regular expressions, do it without. They are very cpu intensive. No reason to use them for this at all.

correction to the above post

A reader, June 27, 2009 - 10:40 am UTC

Assuming you are on 10g or higher , Regular Expression can be used .

IS_NUMBER function

karthick pattabiraman, June 29, 2009 - 1:20 am UTC

Another option is to use TO_NUMBER function in a user defined function.

SQL> create or replace function is_number(pVal in varchar2) return number deterministic
  2  as
  3     lNum number;
  4  begin
  5     lNum := to_number(pVal);
  6     return 1;
  7  exception
  8     when value_error then
  9             return 0;
 10  end;
 11  /

Function created.

SQL> create table t(val varchar2(10), val1 varchar2(100))
  2  /

Table created.

SQL> create index t_idx on t(is_number(val))
  2  /

Index created.

SQL> insert into t values('karthick',rpad('*',100,'*'))
  2  /

1 row created.

SQL> insert into t values('123',rpad('*',100,'*'))
  2  /

1 row created.

SQL> insert into t values('123.33',rpad('*',100,'*'))
  2  /

1 row created.

SQL> insert into t values('.33',rpad('*',100,'*'))
  2  /

1 row created.

SQL> insert into t values('$33',rpad('*',100,'*'))
  2  /

1 row created.

SQL> insert into t values('00033',rpad('*',100,'*'))
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

PL/SQL procedure successfully completed.


SQL> select * from t where is_number(val) = 1
  2  /

VAL        VAL1
---------- ----------------------------------------------------------------------------------------------------
123        ****************************************************************************************************
123.33     ****************************************************************************************************
.33        ****************************************************************************************************
00033      ****************************************************************************************************

SQL> explain plan for
  2  select * from t where is_number(val) = 1
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     3 |   321 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     3 |   321 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     3 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("SYSADM"."IS_NUMBER"("VAL")=1)

14 rows selected.

Tom Kyte
July 06, 2009 - 6:04 pm UTC

given the specification above, that would be overkill.


unless and until the poster says "this is what I really need to do", the replace/translate trick is the most appropriate and efficient.

Yet another option

Oleksandr Alesinskyy, June 29, 2009 - 5:10 am UTC

there is one more option - ask yourself "why this very requirement exists and what is a real problem to be solved"). I'm pretty sure that original requirement is not original but rather "suggested solution" for some other problem - any very like a suboptimal (if not plainly wrong) solution.

So we need to ask Priti "what for you need that select?".
Tom Kyte
July 06, 2009 - 6:12 pm UTC

excellent, 100% true.

There are two answers to all technical questions I like to say, and they are:

a) WHY (why do you want to do that - get the goal, not their 'solution')
b) IT DEPENDS (need more information, probably, this was an "it depends" situation)

A reader, June 29, 2009 - 4:30 pm UTC

How about storing numbers in numeric fields, and text in varchar fields?
Tom Kyte
July 06, 2009 - 6:34 pm UTC

that would make too much sense and be far too practical

Regular Expression version

Jason Ennor, July 02, 2009 - 5:37 pm UTC

SELECT * FROM priti
WHERE REGEXP_LIKE(val1, '^[:0123456789:]+$');


Here is a version using the POSIX notation:

SELECT * FROM priti
WHERE REGEXP_LIKE(val1, '^[[:digit:]]+$');

REGEULAR EXPRESSION

A reader, July 02, 2009 - 7:56 pm UTC

SELECT * FROM priti
WHERE REGEXP_LIKE(val1, '0-9']+$');


is date?

A reader, July 07, 2009 - 6:24 am UTC

Hi Tom,

We have a table which has data of mixed values. We want to filter date values only, which exists in the format OCT-09, NOV-09....
Can we do through translate function or some other way?

Thanks for your help.

Regards,

Tom Kyte
July 07, 2009 - 6:22 pm UTC

well, you know that the length of field must be six and substr of field from 1 for three must be in a set, and you know that the substr from 4 for 1 must be a dash and the substr from 5 for 2 must be a pair of digits.....


     case when substr( c,1,3) in ('JAN','FEB',....,'DEC') and
          substr( c,4,1) = '-'
          and replace( translate( substr(c,5,2), 
                       '0123456789', '0000000000' ), '0', '' ) is null
          and length(c) = 6
      then 1
      else 0
end



IS_DATE output as string or date?

Duke Ganote, July 08, 2009 - 9:53 am UTC

Or, if the result set needs to be date-typed, the usual just-make-Oracle-attempt-the-conversion and only take the successes.

  1  create or replace function MON_YY_ONLY
  2    (p_num in varchar2)
  3    return date
  4  as
  5    x date;
  6  begin
  7    x := to_date (p_num,'MON-YY');
  8    return x;
  9  exception
 10    when others then return null;
 11* end MON_YY_ONLY;
SQL> /

Function created.

SQL> ed
Wrote file afiedt.buf

  1  select mon_yy_only(value)
  2    from
  3  ( select 'OCT-08' value
  4      from dual
  5  union all
  6    select 'xs'
  7*     from dual )
SQL> /

MON_YY_ON
---------
01-OCT-08

Check this

Amit, July 09, 2009 - 4:00 am UTC

Tom Kyte
July 14, 2009 - 2:11 pm UTC

which is sort of the same thing I said in the first place...

I do not understand Amits OraMag code tip?

Kim Berg Hansen, July 15, 2009 - 10:16 am UTC

I do not understand the OraMag code tip that Amit links to in the previous review? I do not hope that any of Tom's readers follow that link and think Tom endorses that way :-). (And I am a bit dissapointed in the OraMag editors that they have not testet a tip before publishing...)

Amits way:
translate( '.1234567890', name, '$' )
That means: "in the string '.1234567890' replace every occurence of the first character in Name with a dollar sign and replace every occurence of the rest of the characters in Name with null." Does that make sense?

Toms way would be:
replace( translate( name, '0123456789', '0000000000' ), '0', '' )
That means: "in the string Name replace every occurence of a digit with '0' - then replace every '0' with null." That makes sense :-)

My own way would be:
translate( name, '§0123456789', '§' )
That means: "in the string Name replace every occurence of a dollarsign with a dollarsign and replace every occurrence of a digit with null."
The dollarsign could be an X or any other character since it is replaced by itself. There just needs to be a character because of the way translate works.
This way may be a little less "self-documenting" than Toms way, but it only uses one function call, not two :-)
So the original question could then use:
where translate( name, '§0123456789', '§' ) is null


A short modified test on part of the data from Amits OraMag tip reveals that Amits way does not do the job but both Toms and my way works:

SQL> with Vtable as (
   select 'TEJWANT 0SINGH' name from dual union all
   select 'SUKHDEV 11DARIRA' name from dual union all
   select 'BILL 22GATES' name from dual
)
select
name,
translate( '.1234567890', name, '$' ) amit,
replace( translate( name, '0123456789', '0000000000' ), '0', '' ) tom,
translate( name, '$0123456789', '$' ) kim
from Vtable

NAME             AMIT        TOM              KIM             
---------------- ----------- ---------------- ----------------
TEJWANT 0SINGH   .123456789  TEJWANT SINGH    TEJWANT SINGH   
SUKHDEV 11DARIRA .234567890  SUKHDEV DARIRA   SUKHDEV DARIRA  
BILL 22GATES     .134567890  BILL GATES       BILL GATES      

3 rows selected.


No complaints about you, Tom - mainly about quality of OraMag Code Tips :-)


Tom Kyte
July 15, 2009 - 12:23 pm UTC

hah, I'm feeling recursive all of sudden

http://asktom.oracle.com/Misc/birth-of-asktom.html

read about how this asktom thing got started in the first place..


Yes, I just glanced at the tip, I saw the translate and just assumed it was the same/similar technique

Argh - typo :-)

Kim Berg Hansen, July 15, 2009 - 10:22 am UTC

Dang - a typo in my review above. I have used a § sign in some of the text and $ sign in my example and other bits of the text.
Sorry about that, but I think the meaning is reasonably clear anyway :-)

Now I understand part of Amits way :-)

Kim Berg Hansen, July 15, 2009 - 10:39 am UTC

Sorry about not keeping this in one review :-) But I just understood the second part of Amits OraMag tip.

He does this:
where length(translate('1234567890','$'||name,'$')) = 10

Whereas I would do:
where translate(name,'$0123456789','$') is null

His idea is if there are any digits in Name, the translate will remove part of the string '1234567890', and thus the length would be less than 10.

Presumbably that is meant to be more efficient for larger strings, because only 10 characters need to be passed through the translate algorithm. On the other hand the "defining" string for translate would be larger and probably contain lots of duplicates. So it would probably depend on the implementation of the translate algorithm whether or not it is more efficient to do 10 "large" translates or a large number of "small" translates... :-)

I take part of my critique of Amit back - the where clause of his OraMag tip makes sense now. But I still don't see what's the point in his select :-)

Find the rows containing only 2 alphabets from a-z irrespective of case

Soumadip, March 23, 2012 - 7:06 am UTC

Hi Tom,

My requirement is to find any row which contains exacts 2 alphabets from a-z or A-Z and may contain any numbers or spaces but not any more alphabet.

CREATE TABLE t (x VARCHAR2(10));

INSERT ALL
INTO t VALUES ('XYZ123')
INTO t VALUES ('XYZ 123')
INTO t VALUES ('xyz 123')
INTO t VALUES ('X1Y2Z3')
INTO t VALUES ('123xyz')
INTO t VALUES ('123XYZ')
INTO t VALUES ('XY12')
INTO t VALUES ('X1Z3')
INTO t VALUES ('X123Y')
INTO t VALUES ('12 xy')
INTO t VALUES ('xy 12')
SELECT *
FROM dual;

SELECT * FROM t;

Output
------
XYZ123
XYZ 123
xyz 123
X1Y2Z3
123xyz
123XYZ
XY12
X1Z3
X123Y
12 xy
xy 12

I want to write a query which will return the following output :-

Output
------
XY12
X1Z3
X123Y
12 xy
xy 12

Any help in the case will be much appreciated.
Tom Kyte
March 23, 2012 - 8:37 am UTC

ops$tkyte%ORA11GR2> CREATE TABLE t (x VARCHAR2(10));

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> INSERT ALL
  2      INTO t VALUES ('XYZ123')
  3      INTO t VALUES ('XYZ 123')
  4      INTO t VALUES ('xyz 123')
  5      INTO t VALUES ('X1Y2Z3')
  6      INTO t VALUES ('123xyz')
  7      INTO t VALUES ('123XYZ')
  8      INTO t VALUES ('XY12')
  9      INTO t VALUES ('X1Z3')
 10      INTO t VALUES ('X123Y')
 11      INTO t VALUES ('12 xy')
 12      INTO t VALUES ('xy 12')
 13   SELECT *
 14     FROM dual;

11 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select *
  2    from t
  3   where length(x)-length(replace( translate(lower(x),'abcdefghijklmnopqrstuvwxyz','a'), 'a', '' ) ) = 2
  4  /

X
----------
XY12
X1Z3
X123Y
12 xy
xy 12

ops$tkyte%ORA11GR2> 

Awesome

Soumadip, March 23, 2012 - 10:46 am UTC

Thanks Tom, awesome as always.

Select only numeric vals from a varchar col

Bindu, October 16, 2012 - 8:34 am UTC

Found it very useful. I had to eliminate numeric values in my sql statement and used this one - worked like a charm since in my case if the first letter is non numeric then i eliminate it.

SELECT
a.account AS Account,
a.descr AS Descr
FROM ps_gl_account_tbl a
WHERE a.account >= '300000'
AND REGEXP_LIKE(SUBSTR(a.account,1,1), '[[:digit:]]')
AND a.effdt = (SELECT MAX(b.effdt) FROM ps_gl_account_tbl b
WHERE b.ACCOUNT = a.ACCOUNT)
ORDER BY a.account


Tom Kyte
October 16, 2012 - 10:02 am UTC

this would be better written without the cpu intensive regexp functions.


and translate( substr( a.account, 1, 1), '0123456789', '0000000000' ) = 0

also, your

a.account > '300000'

doesn't seem "right"

ops$tkyte%ORA11GR2> select * from dual where '4' >= '300000';

D
-
X

ops$tkyte%ORA11GR2> 


did you really mean to do that?

Questions for negative values

jp, April 11, 2013 - 7:59 pm UTC

Tom
I tried your option of replace and translate. How can I use this code for negative numbers and float data type?

select replace( translate( '-123.45', '0123456789', '0000000000' ), '0',
'' ) from dual;

it is returning '-.' sign. As it is a char field I dont want to replace that with null.

Could you please help me out with this?


Tom Kyte
April 22, 2013 - 3:02 pm UTC

you'd have to do a little more work to ensure that the - is a leading thing and that a '.' happens at most once.


ops$tkyte%ORA11GR2> select n
  2    from (select '-123.45' n from dual)
  3   where nvl(replace( translate( ltrim( n, '-' ), '0123456789', '0000000000' ), '0', '' ),'.') = '.'
  4  /

N
-------
-123.45




ltrim off the -

add a '.' if the returned value is null - and then make sure it just is '.' when all is done..

selecting numeric value and filtering

AG, October 21, 2013 - 9:59 am UTC

Dear Tom,

my query is as give below

I have created the table and inserted the records as per given sql.

CREATE TABLE A1 (T1 VARCHAR2(4), T2 VARCHAR2(4));

INSERT INTO A1 VALUES ('A307', '10');
INSERT INTO A1 VALUES ('A307', '20');
INSERT INTO A1 VALUES ('A307', '30');
INSERT INTO A1 VALUES ('A307', '40');
INSERT INTO A1 VALUES ('A307', '50');
INSERT INTO A1 VALUES ('A307', '60');
INSERT INTO A1 VALUES ('A307', '70');
INSERT INTO A1 VALUES ('A307', '80');
INSERT INTO A1 VALUES ('A307', '90E');

COMMIT;

SELECT TO_NUMBER(T2) FROM (
select T2, TRANSLATE (T2, '0123456789', '0000000000' ) X1 from A1 WHERE TRANSLATE (T2, '0123456789', '0000000000' ) = '00')
WHERE T2=50

while executing the above query, I am getting the invalid number error although inner query returns only numeric values. As soon as I use to_number query starts giving error
Tom Kyte
November 01, 2013 - 8:30 pm UTC

that is because that query is to us not any different than:

Select to_number(T2), TRANSLATE (T2,  '0123456789', '0000000000' ) X1 
  from A1 
 WHERE TRANSLATE (T2,  '0123456789', '0000000000' ) = '00')
   and to_number(t2) = 50



this happens every time someone decides "i shall stick numbers into strings and be so so smart".

every time...

every single time...


use

case TRANSLATE (T2, '0123456789', '0000000000' ) = '00') then to_number(t2) end


instead of just to_number(t2).



Query

Sebastian, January 23, 2014 - 1:32 pm UTC

SELECT <column_name>
FROM <table_name>
WHERE NVL(LENGTH(REPLACE( translate( <column_name>, '0123456789', '0000000000' ), '0', '' )), 0) = 0