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

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

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

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>

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

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.

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?".

How about storing numbers in numeric fields, and text in varchar fields?

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


Here is a version using the POSIX notation:

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

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

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,

     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

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

https://asktom.oracle.com/magazine-archive.htm

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 :-)

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 :-)

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 :-)

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.

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> 

Thanks Tom, awesome as always.

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

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

D
-
X

ops$tkyte%ORA11GR2> 

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?

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

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

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

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