Results 1 to 4 of 4

Thread: Is Numeric

  1. #1
    Join Date
    Feb 2004
    Posts
    86

    Unanswered: Is Numeric

    I need to update a column in my database which is a varchar2, however I need to update only fields that are not numeric. Is there a function in oracle that will handle this? It can be put into a stored procedure to handle this.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I suggest creating your own function

    or, in your procedure accoutn for error handling and it should be fine.

    to_number(column name)

    then error handle when others
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    This should be what you're looking for:

    Code:
    create or replace function isnumeric (p_string in varchar2) return boolean as
      v_number number;
    begin
      v_number := p_string;
      return TRUE;
    exception
      when others then return FALSE;
    end;
    Saludos...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    DKG's function does the job perfectly; but for use in SQL you would need to amend so that the return value is something other than BOOLEAN - e.g. return INTEGER, with 1 for TRUE and 0 for FALSE. Then you can use it like this:

    update table1
    set ...
    wheer isnumeric(col1) = 1;

Posting Permissions

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