Re: Using TRANSLATE() to detect numeric strings

From: Niall Litchfield <>
Date: Fri, 25 Jan 2008 16:41:28 +0000
Message-ID: <>

Interesting indeed. I've always used the is_numeric function (and possibly indexed the column on it.)

What's that I hear you say? You mean you don't have an is_numeric function available.
Mine reads

create or replace function is_numeric(p_stringval in varchar2) return number is
  l_numval number;
  l_numval := to_number(p_stringval);
  return 1;
  when others then
  return 0;
end is_numeric;

I've never done the runstats thing though. On Jan 25, 2008 4:11 PM, Jared Still <> wrote:

> This week I found it necessary to differentiate between strings that
> were composed of numeric data and those that were alphanumeric.
> This was kind of fun an interesting.
> It was also too long to post here, so I put it on my blog.
> if you're interested.
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist

Niall Litchfield
Oracle DBA

Received on Fri Jan 25 2008 - 10:41:28 CST

Original text of this message