Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Unanswered: PL/SQL - How can I verify presence of alphanumeric chars in a text field

    I've a text column in my table. This col contains mostly numbers, but in some cases can contain alphanumeric value also. In my PL/SQL code, amongst other things, I would like to check if this particular col has such alphanumeric value in case of which I'd like to take certain action, else continue normal flow of program.

    Any suggestions on how it could be done?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You can use regexp_like to only retrieve the rows you are interested in:

    http://download-uk.oracle.com/docs/c...7.htm#i1048942

    Code:
    SELECT *
    FROM the_unknown_table
    WHERE REGEXP_LIKE(the_unknow_column, '^[0-9]+$');
    or inside a PL/SQL block
    Code:
      if regexp_like(some_value, '^[0-9]+$') then
         dbms_output.put_line('number');
      else
         dbms_output.put_line('not a number');
      end if;
    Last edited by shammat; 08-27-10 at 07:05. Reason: Added PL/SQL example

  3. #3
    Join Date
    Aug 2010
    Posts
    6
    Thanks, that's quite useful. Unfortunately it seems it works 10g onwards. I'm having 9i

    Any other suggestions that could work in 9i?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MxC View Post
    Thanks, that's quite useful. Unfortunately it seems it works 10g onwards. I'm having 9i

    Any other suggestions that could work in 9i?
    Wow, that is an ancient version.
    (That'll teach you to include your version number, especially when using a non-supported version )


    The only workaround I can see, is to create a function that tries to convert a string to a number and catches all exceptions. If an exception occurred you ignore it and signal that it was not a number, something like:

    Code:
    create or replace function safe_to_number(to_test varchar)
    return integer
    is
      result integer;
    begin
      begin
        result := to_number(to_test);
      exception when others then
        result := null;
      end;
      return result;
    end;
    /
    You will need to adjust the to_number() call to use your expected number format.

    But be warned: this is not a really fast solution, especially when you have a lot of exceptions!

  5. #5
    Join Date
    Aug 2010
    Posts
    6
    Thanks, that is a useful suggestion indeed.
    BTW, the lesson was learnt immediately when realized that your solution worked only on 10g

Posting Permissions

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