[sql] Finding rows that don't contain numeric data in Oracle

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

This question is related to sql oracle varchar numeric varchar2

The answer is


From http://www.dba-oracle.com/t_isnumeric.htm

LENGTH(TRIM(TRANSLATE(, ' +-.0123456789', ' '))) is null

If there is anything left in the string after the TRIM it must be non-numeric characters.


enter image description hereI tray order by with problematic column and i find rows with column.

SELECT 
 D.UNIT_CODE,
         D.CUATM,
         D.CAPITOL,
          D.RIND,
          D.COL1  AS COL1


FROM
  VW_DATA_ALL_GC  D
  
  WHERE
  
   (D.PERIOADA IN (:pPERIOADA))  AND   
   (D.FORM = 62) 
   AND D.COL1 IS NOT NULL
 --  AND REGEXP_LIKE (D.COL1, '\[\[:alpha:\]\]')
 
-- AND REGEXP_LIKE(D.COL1, '\[\[:digit:\]\]')
 
 --AND REGEXP_LIKE(TO_CHAR(D.COL1), '\[^0-9\]+')
 
 
   GROUP BY 
    D.UNIT_CODE,
         D.CUATM,
         D.CAPITOL,
          D.RIND ,
          D.COL1  
         
         
        ORDER BY 
        D.COL1

After doing some testing, building upon the suggestions in the previous answers, there seem to be two usable solutions.

Method 1 is fastest, but less powerful in terms of matching more complex patterns.
Method 2 is more flexible, but slower.

Method 1 - fastest
I've tested this method on a table with 1 million rows.
It seems to be 3.8 times faster than the regex solutions.
The 0-replacement solves the issue that 0 is mapped to a space, and does not seem to slow down the query.

SELECT *
FROM <table>
WHERE TRANSLATE(replace(<char_column>,'0',''),'0123456789',' ') IS NOT NULL;

Method 2 - slower, but more flexible
I've compared the speed of putting the negation inside or outside the regex statement. Both are equally slower than the translate-solution. As a result, @ciuly's approach seems most sensible when using regex.

SELECT *
FROM <table>
WHERE NOT REGEXP_LIKE(<char_column>, '^[0-9]+$');

In contrast to SGB's answer, I prefer doing the regexp defining the actual format of my data and negating that. This allows me to define values like $DDD,DDD,DDD.DD In the OPs simple scenario, it would look like

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^[0-9]+$');

which finds all non-positive integers. If you wau accept negatiuve integers also, it's an easy change, just add an optional leading minus.

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+$');

accepting floating points...

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+(\.[0-9]+)?$');

Same goes further with any format. Basically, you will generally already have the formats to validate input data, so when you will desire to find data that does not match that format ... it's simpler to negate that format than come up with another one; which in case of SGB's approach would be a bit tricky to do if you want more than just positive integers.


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]+');

I've found this useful:

 select translate('your string','_0123456789','_') from dual

If the result is NULL, it's numeric (ignoring floating point numbers.)

However, I'm a bit baffled why the underscore is needed. Without it the following also returns null:

 select translate('s123','0123456789', '') from dual

There is also one of my favorite tricks - not perfect if the string contains stuff like "*" or "#":

 SELECT 'is a number' FROM dual WHERE UPPER('123') = LOWER('123')

Use this

SELECT * 
FROM TableToSearch 
WHERE NOT REGEXP_LIKE(ColumnToSearch, '^-?[0-9]+(\.[0-9]+)?$');

After doing some testing, i came up with this solution, let me know in case it helps.

Add this below 2 conditions in your query and it will find the records which don't contain numeric data

 and REGEXP_LIKE(<column_name>, '\D') -- this selects non numeric data
 and not REGEXP_LIKE(column_name,'^[-]{1}\d{1}') -- this filters out negative(-) values

You can use this one check:

create or replace function to_n(c varchar2) return number is
begin return to_number(c);
exception when others then return -123456;
end;

select id, n from t where to_n(n) = -123456;

To get an indicator:

DECODE( TRANSLATE(your_number,' 0123456789',' ')

e.g.

SQL> select DECODE( TRANSLATE('12345zzz_not_numberee',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"contains char"

and

SQL> select DECODE( TRANSLATE('12345',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"number"

and

SQL> select DECODE( TRANSLATE('123405',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"number"

Oracle 11g has regular expressions so you could use this to get the actual number:

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

COL1
----------
47845
48543
12
...

If there is a non-numeric value like '23g' it will just be ignored.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to varchar

SQL Server date format yyyymmdd What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte? Difference between VARCHAR and TEXT in MySQL PostgreSQL: ERROR: operator does not exist: integer = character varying Can I use VARCHAR as the PRIMARY KEY? Is the LIKE operator case-sensitive with MSSQL Server? How to convert Varchar to Double in sql? SQL Server : error converting data type varchar to numeric What is the MySQL VARCHAR max size? SQL Server Convert Varchar to Datetime

Examples related to numeric

How to convert entire dataframe to numeric while preserving decimals? What's the difference between integer class and numeric class in R IsNumeric function in c# How to compare numbers in bash? Right way to convert data.frame to a numeric matrix, when df also contains strings? angularjs: allows only numbers to be typed into a text box How to convert Varchar to Double in sql? SQL Server : error converting data type varchar to numeric How do I convert certain columns of a data frame to become factors? How to create a numeric vector of zero length in R

Examples related to varchar2

What is the max size of VARCHAR2 in PL/SQL and SQL? Finding rows that don't contain numeric data in Oracle