Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: Using PL/SQL , how do I identify if a string is numeric?

    Using PL/SQL , how do I identify if a string is numeric? I need to move the value if numeric to a variable and compare that variable to another. The item in question is in the last row of a text file, a trailer record.

    Thanks,

    Will

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use logic like this:
    Code:
    declare
      v_string varchar2(12) := '123.45';
      v_number number;
    begin
      v_number := v_string;
      -- Yes, it was a number (no exception)
    exception
      when others then
        -- Not a number
    end;
    It could be implemented as a function that returns 1 for yes, 0 for no like this:
    Code:
    create or replace function is_number (p_string in varchar2) return integer is
      v_number number;
    begin
      v_number := p_string;
      return (1);
    exception
      when others then
        return (0);
    end;
    Then it can be useed even in SQL, like this:
    Code:
    select * from t
    where is_number(col) = 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
  •