Home » SQL & PL/SQL » SQL & PL/SQL » Is numeric ?
Is numeric ? [message #10309] Wed, 14 January 2004 07:03 Go to next message
Mark
Messages: 284
Registered: July 1998
Senior Member
I have to delete all records from a table which have non-numeric values for one particular column.

Is there a function in Oracle like IsNumber or IsNumeric, so I can test it for being a numeric ?

I tried to manipulate with to_number function, but it does not return like True or False.

Any suggestions ?

Thanks.

 

 
Re: Is numeric ? [message #10310 is a reply to message #10309] Wed, 14 January 2004 08:14 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
Try

SELECT your_column FROM your_table WHERE
REPLACE(TRANSLATE(SUBSTR(your_column,
LENGTH(your_column) -LENGTH(your_column), LENGTH(your_column)), '1234567890','0000000000'), '0', '') IS
NOT NULL;
Re: Is numeric ? [message #10323 is a reply to message #10309] Wed, 14 January 2004 19:01 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

you can try this logic also :

nvl(length(trim(translate(ur_column,'1234567890',' '))),0) > 0
Re: Is numeric ? [message #10328 is a reply to message #10309] Wed, 14 January 2004 22:56 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Although there isn't a built-in is_number function, it is simple to create a user-defined is_number function. If you use only combinations of built-in functions like length, replace, and translate, you may miss some valid numbers that have plus or minus signs, decimal points, or scientific notation. If you modify such usage to allow for such things, then you may incorrectly keep things with multiple hypphens, periods, and such, that are not numbers. So, it is best to create a user-defined function that attempts a to_number conversion and handles a value error. However, if you are expecting a high number of non-numeric values, then using a combination of built-in functions, like translate, replace, and decode, along with the user-defined function is the most efficient, since built-in functions are faster, and it will reduce calls to the user-defined function. Please see the examples below.

scott@ORA92> -- test table and test data:
scott@ORA92> SELECT * FROM your_table
  2  /

YOUR_COLUMN
-----------
1
A
1.1-2

scott@ORA92> --
scott@ORA92> --
scott@ORA92> -- is_number function:
scott@ORA92> CREATE OR REPLACE FUNCTION is_number
  2    (p_string IN VARCHAR2)
  3    RETURN	    VARCHAR2
  4  AS
  5    v_string     NUMBER;
  6  BEGIN
  7    v_string := TO_NUMBER (p_string);
  8    RETURN 'Y';
  9  EXCEPTION
 10    WHEN VALUE_ERROR THEN
 11  	 RETURN 'N';
 12  END is_number;
 13  /

Function created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> --
scott@ORA92> --
scott@ORA92> -- usage of is_number function alone:
scott@ORA92> DELETE FROM your_table
  2  WHERE  is_number (your_column) = 'N'
  3  /

2 rows deleted.

scott@ORA92> SELECT * FROM your_table
  2  /

YOUR_COLUMN
-----------
1

scott@ORA92> ROLLBACK
  2  /

Rollback complete.

scott@ORA92> --
scott@ORA92> --
scott@ORA92> -- usage of is_number function and built-in functions:
scott@ORA92> DELETE FROM your_table
  2  WHERE
  3  DECODE (REPLACE (TRANSLATE (your_column, 'E+-1234567890.', '00000000000000'), '0', ''),
  4  	     NULL, is_number (your_column),
  5  	     'N') = 'N'
  6  /

2 rows deleted.

scott@ORA92> SELECT * FROM your_table
  2  /

YOUR_COLUMN
-----------
1
Previous Topic: ORA-06512 & ORA-04088 error message but my trigger compiles without errors!?
Next Topic: PL/SQL
Goto Forum:
  


Current Time: Fri Mar 29 09:50:19 CDT 2024