Menu
I am trying to locate some problematic records in a very large Oracle table. The column should contain all numeric data even though it is a varchar2 column. I need to find the records which don't contain numeric data (The to_number(col_name) function throws an error when I try to call it on this column).
213
This is the way: SELECT * FROM TABLE_NAME WHERE NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$ '); This also excludes values which contain decimals.
IF(option_id = 0021) THEN IF((value<10000) or (value>7200000) or /* Numeric Check */)THEN ip_msg(6214,option_name); -- Error Message return; END IF; END IF; In SQL Server, I simply used ISNUMERIC() .
Answer: To test a string for numeric characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle. The string value that you are testing.
You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM employees WHERE ROWNUM < 10; If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed.
I was thinking you could use a regexp_like condition and use the regular expression to find any non-numerics. I hope this might help?!
SELECT * FROM table_with_column_to_search WHERE REGEXP_LIKE(varchar_col_with_non_numerics, '[^0-9]+'); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
