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

MxC · #1 (**permalink**) 08-27-10, 05:34

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?

shammat · #2 (**permalink**) 08-27-10, 06:02

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;

MxC · #3 (**permalink**) 08-27-10, 06:50

Thanks, that's quite useful. Unfortunately it seems it works 10g onwards. I'm having 9i

Any other suggestions that could work in 9i?

shammat · #4 (**permalink**) 08-27-10, 07:15

Quote:

Originally Posted by MxC

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!

MxC · #5 (**permalink**) 08-27-10, 08:23

Thanks, that is a useful suggestion indeed.
BTW, the lesson was learnt immediately when realized that your solution worked only on 10g

Internet Services

Web Development

Digital Marketing

Consumer Tech