I need assistance creating a SQL Oracle running in Oracle Database to fetch only those columns which has NO NUMERIC VALUES

Question

I have tried with this method:

SELECT column1 FROM table WHERE column1 not like '%[^0-9]%' and column1 != ''

but nothing really is happening, I need to filter a column which has purely no numeric values in it.

Answer 1

This expression:

column1 not like '%[^0-9]%'

Does not do what you think. It matches strings that look like:

 abasdfs[^0-9]asfsdf
 ^-----^ any string
        ^----^ exactly this
              ^-----^ any string

Presumably, you want a string that only contains digits. I would suggest one of:

regexp_like(column1, '^[0-9]*$')
not regexp_like(column1, '[^0-9]')

Answer 2

Assuming you want numbers only (not necessarily the same as numeric values), you could also use the VALIDATE_CONVERSION function. That returns 1 if conversion to a datatype is successful. Check the docs.

SELECT column1 
  FROM table 
 WHERE VALIDATE_CONVERSION(column1 AS NUMBER) = 1

example:

sql>create table test_table(column1 VARCHAR2(10));

Table TEST_TABLE created.

sql>INSERT INTO test_table (column1) VALUES (100);

1 row inserted.

sql>INSERT INTO test_table (column1) VALUES ('TEXT');

1 row inserted.

sql>SELECT column1 
  2    FROM test_table 
  3*  WHERE VALIDATE_CONVERSION(column1 AS NUMBER) = 1;

COLUMN1   
----------
100