I have to create an exception that ignores string data from an int table-CodePudding

this is my first post here, so i would like to ask you some help. I'm supposed to get the average from a table with int data, but it also has string data. I need to create an exception that gets the avg ignoring the string datas

   create table x (y varchar(10));
    insert into x values (5);
    insert into x values (1);
    insert into x values (2);
    insert into x values (14);
    insert into x values (3);
    insert into x values ('a');
insert into x values ('asdds');
select avg(y) from x

CodePudding user response：

With data you posted, see if this helps:

SQL> select avg(y) from x
  2  where regexp_like(y, '^[[:digit:]] $');

    AVG(Y)
----------
         5

SQL>

CodePudding user response：

Use TO_NUMBER as it will handle all valid numbers:

SELECT AVG(TO_NUMBER(y DEFAULT NULL ON CONVERSION ERROR)) As avg_y
FROM   x

Which, for the sample data:

create table x (y) AS
SELECT '5'      FROM DUAL UNION ALL
SELECT '1e0'    FROM DUAL UNION ALL -- This is a valid number!
SELECT '2.0'    FROM DUAL UNION ALL
SELECT '0.14e2' FROM DUAL UNION ALL -- So is this!
SELECT '3'      FROM DUAL UNION ALL
SELECT 'a'      FROM DUAL UNION ALL
SELECT 'assdds' FROM DUAL;

Outputs:

AVG_Y

5

AVG_Y
5

db<>fiddle here