Re: does string contain a valid number
- From: Adric Norris <spikey.mcmarbles@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 18 Aug 2009 15:16:50 -0500
On Tue, Aug 18, 2009 at 13:19, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:
> What is the best way to see if a string contains a valid number? 10gR2
>
> thanks,
> Mike
>
The best solution is, of course, to simply use the correct datatype in the
first place. When it's too late for the up-front approach, I've
occasionally seen a function similar to the one below used as a workaround.
create or replace function is_number(p_number IN varchar2, p_format IN
varchar2 default NULL)
return number
IS
v_number number;
BEGIN
BEGIN
if p_format is NULL then
v_number := to_number(p_number);
else
v_number := to_number(p_number, p_format);
end if;
EXCEPTION
when VALUE_ERROR then
v_number := NULL;
END;
return v_number;
END;
/
An alternative is to validate the string using a regular expression, but
this can get rather awkward if the numeric representations aren't fairly
uniform and straightforward.
Regardless of the method chosen, you'll pay a steep performance penalty if
running against a large volume of data.
--
"I'm too sexy for my code." - Awk Sed Fred.
Other related posts:
