Finding rows that don t contain numeric data in Oracle

Question

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

User · Answer

To get an indicator   DECODE  TRANSLATE your number   0123456789         e g   SQL gt  select DECODE  TRANSLATE  12345zzz not numberee    0123456789        NULL   number   contains char    2 from dual  3     contains char    and  SQL gt  select DECODE  TRANSLATE  12345    0123456789        NULL   number   contains char    2 from dual  3     number    and  SQL gt  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 gt  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

User · Answer

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

User · Answer

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

User · Answer

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  lt column name gt     D   -- this selects non numeric data  and not REGEXP LIKE column name    -  1  d 1    -- this filters out negative -  values

User · Answer

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  lt table gt  WHERE TRANSLATE replace  lt char column gt   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  lt table gt  WHERE NOT REGEXP LIKE  lt char column gt      0-9

User · Answer

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

User · Answer

Use this  SELECT    FROM TableToSearch  WHERE NOT REGEXP LIKE ColumnToSearch    -  0-9      0-9

User · Answer

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

User · Answer

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

User · Answer

I 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

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

Examples related to sql

Examples related to oracle

Examples related to varchar

Examples related to numeric

Examples related to varchar2