By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table a (col1, col2) as
select '42', 'abc' from dual union all
select '3.142', '42' from dual;
2 rows affected
create table b (col1 varchar2(1));
-- original
create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
RETURN NUMBER
AS
BEGIN
EXECUTE IMMEDIATE('SELECT TO_NUMBER(nvl(' ||column_name|| ',0)) from ' ||table_name);
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
/
select table_name, column_name, is_column_numberic(table_name, column_name)
from user_tab_columns
where data_type = 'VARCHAR2' -- others are available, but exclude numbers/dates/etc?
| TABLE_NAME | COLUMN_NAME | IS_COLUMN_NUMBERIC(TABLE_NAME,COLUMN_NAME) |
|---|---|---|
| A | COL1 | 1 |
| A | COL2 | 1 |
| B | COL1 | 1 |
-- OldProgrammer
create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
RETURN NUMBER
AS
val NUMBER;
BEGIN
EXECUTE IMMEDIATE('SELECT TO_NUMBER(nvl(' ||column_name|| ',0)) from ' ||table_name)
INTO val;
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
/
select table_name, column_name, is_column_numberic(table_name, column_name)
from user_tab_columns
where data_type = 'VARCHAR2' -- others are available, but exclude numbers/dates/etc?
| TABLE_NAME | COLUMN_NAME | IS_COLUMN_NUMBERIC(TABLE_NAME,COLUMN_NAME) |
|---|---|---|
| A | COL1 | 0 |
| A | COL2 | 0 |
| B | COL1 | 0 |
-- OldProgrammer with MAX and reordered functions
create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
RETURN NUMBER
AS
val NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT NVL(MAX(TO_NUMBER(' ||column_name|| ')),0) from ' ||table_name
INTO val;
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
/
select table_name, column_name, is_column_numberic(table_name, column_name)
from user_tab_columns
where data_type = 'VARCHAR2' -- others are available, but exclude numbers/dates/etc?
| TABLE_NAME | COLUMN_NAME | IS_COLUMN_NUMBERIC(TABLE_NAME,COLUMN_NAME) |
|---|---|---|
| A | COL1 | 1 |
| A | COL2 | 0 |
| B | COL1 | 1 |
-- With validate_conversion
create or replace FUNCTION is_column_numberic (table_name IN VARCHAR2, column_name in VARCHAR2)
RETURN NUMBER
AS
l_result NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT MIN(VALIDATE_CONVERSION(' ||column_name|| ' AS NUMBER)) from ' ||table_name
INTO l_result;
RETURN l_result;
END;
/
select table_name, column_name, is_column_numberic(table_name, column_name)
from user_tab_columns
where data_type = 'VARCHAR2' -- others are available, but exclude numbers/dates/etc?
| TABLE_NAME | COLUMN_NAME | IS_COLUMN_NUMBERIC(TABLE_NAME,COLUMN_NAME) |
|---|---|---|
| A | COL1 | 1 |
| A | COL2 | 0 |
| B | COL1 | null |