Re: Avoiding ORA-01722 Invalid Number

In article <1118196324.299627.311020_at_g14g2000cwa.googlegroups.com>, Barbara Boehmer says...
> scott_at_ORA92> CREATE TABLE t1 (charcol VARCHAR2(10))
> 2 /
>
> Table created.
>
> scott_at_ORA92> INSERT ALL
> 2 INTO t1 VALUES ('10')
> 3 INTO t1 VALUES ('20')
> 4 INTO t1 VALUES ('30')
> 5 INTO t1 VALUES ('231')
> 6 INTO t1 VALUES ('PEN')
> 7 SELECT * FROM DUAL
> 8 /
>
> 5 rows created.
>
> scott_at_ORA92> CREATE OR REPLACE FUNCTION my_to_number
> 2 (p_string IN VARCHAR2)
> 3 RETURN NUMBER
> 4 AS
> 5 v_num NUMBER;
> 6 BEGIN
> 7 v_num := TO_NUMBER (p_string);
> 8 RETURN v_num;
> 9 EXCEPTION
> 10 WHEN VALUE_ERROR THEN
> 11 RETURN NULL;
> 12 END my_to_number;
> 13 /
>
> Function created.
>
> scott_at_ORA92> SHOW ERRORS
> No errors.
> scott_at_ORA92> SELECT *
> 2 FROM t1
> 3 WHERE my_to_number (charcol) BETWEEN 20 AND 30
> 4 /
>
> CHARCOL
> ----------
> 20
> 30
>
> scott_at_ORA92>
>
>

Thanks - this is the simplest approach.

cheers!

-- 

jeremy