How to get non numeric column values from a table?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to get non numeric column values from a table?

  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Question How to get non numeric column values from a table?

    hi,
    I need to identify all the non numeric values in a table column. The requirement is as follows.

    1. Curently the amount is stored in a VARCHAR2 column ( AMOUNT) of table A.
    2. All the data in this table needs to be migrated to new tables. In new tables these columns are having data type of NUMBER.Since the source column is having data type of VARCHAR2 some of values in AMOUNT have non numeric characters. This is causing lot of trouble in migrating the data to a NUMBER field.
    Is there any way I can run a SQL to scan the table and identify the records that are having non numeric value in AMOUNT column.

    Thanks in advance,
    SCO

  2. #2
    Join Date
    Nov 2002
    Posts
    80
    I'm sure there are better ways but here goes..

    insert into new_table as
    select a, b, c,
    case when UPPER(num_as_varchar) = LOWER(num_as_varchar) then to_number( num_as_varchar)
    else 0.0
    end, d, e from old_table;



    maybe use one of those 0-9 (+ ./,) functions rather than UPPER/LOWER

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The suggested method will probably recognise the majority of nonnumerical values in your VARCHAR2 column, but it will certanly fail on some strings. For example, it will not find the following few samples to be non-numerical:

    909..12
    934%12
    9716+13
    !"#$%&/()=
    etc etc... You get the point.

    In fact, I think it would be "very hard" to solve this in generic maner only with SQL and built-in functions. It would be much easier to write your own simple PL/SQL function and use this in your SQL:
    Code:
    create or replace function my_to_number
      (p_string in varchar2)
      return number
    is
      l_number number := null;
    begin
      begin
        l_number := to_number(p_string);
      exception
        when others then null;
      end;
      return l_number;
    end;
    Now you can write your query to extract only those non-numeric values like:
    Code:
    select * from my_table
    where my_column is not null
      and my_to_number(my_column) is null;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width