Testing a string for a numeric value

Sometimes it is necessary to determine if a value in a varchar column actually contains a number. The code below gives examples of how to do this.

All versions of Oracle

The code below created a stored procedure that can be used on all versions of Oracle to test whether a string contains a numeric value. This stored procedure can be called from either SQL or PL/SQL.

CREATE OR REPLACE FUNCTION is_numeric( p_char varchar2 ) return varchar2 IS
  if (to_number(p_char) is not null) then
    return 'Y'; 
    return 'N';
  end if; 
  when others then return 'N';

This can be used from SQL like so…

select ..... where is_numeric(<column>) = 'Y';

Oracle 10g and Above

Oracle 10g supports the use of regular expressions, so although the PL/SQL procedure above could still be used it will be more efficient to use a regular expression to test for a numeric value. An example of using REGEXP_LIKE from within SQL to detect a numeric value is shown below:

select ..... where regexp_like(<column> , '^-?[0-9]*.?[0-9]*$');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: