Is numeric ? [message #10309] |
Wed, 14 January 2004 07:03 |
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 |
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 #10328 is a reply to message #10309] |
Wed, 14 January 2004 22:56 |
|
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
|
|
|