How to find any variation of the number zero; 0, 0.0, 00.00, 0.000, 000.0, etc

I'm using oracle pl/sql and I have the following sql query.

SELECT column_1 FROM table_1
WHERE  column_1 != null
AND    column_1 != ' '
AND    column_1 != '0'
AND    column_1 != '0.0'
AND    column_1 != '00.00'
AND    column_1 != '000.000'
AND    column_1 != '0.000'
AND    column_1 != '000.0'
etc..
etc..

As you can see, column_1 is a text field. What I need to do is remove anything that is any variation of zero, null, or, empty.

Is there anyway to do this without having to list out each individual variation with a WHERE clause?
Something like this is what I was hoping for, but open to any suggestions.

SELECT column_1 FROM table_1
WHERE  column_1 != null
AND    column_1 != ' '
AND ConvertToNumberFormat(column_1) != ConvertToNumberFormat(0)

You can use the regular expression which matches:

• ^ the start-of-the-string; then
• \s* zero-or-more white-space characters; then
• 0* zero-or-more zero characters; then
• \.? zero-or-one dot character; then
• 0* zero-or-more zero characters; then
• \s* zero-or-more white-space characters; then
• $ the end-of-the-string.

(You need to match the start and end of the string otherwise it could match sub-strings like 400.03.)

Giving the regular expression ^\s*0*\.?0*\s*$ which you can use like this:

Oracle Setup:

CREATE TABLE table_1 ( column_1 ) AS
SELECT ' ' FROM DUAL UNION ALL
SELECT '0' FROM DUAL UNION ALL
SELECT '0.' FROM DUAL UNION ALL
SELECT '.0' FROM DUAL UNION ALL
SELECT '0.0' FROM DUAL UNION ALL
SELECT '00.0' FROM DUAL UNION ALL
SELECT '4.0' FROM DUAL UNION ALL
SELECT '200.0' FROM DUAL UNION ALL
SELECT '.03' FROM DUAL UNION ALL
SELECT ' 00.000' FROM DUAL UNION ALL
SELECT ' 0000 ' FROM DUAL UNION ALL
SELECT NULL FROM DUAL;

Query:

SELECT column_1
FROM   table_1
WHERE  NOT REGEXP_LIKE( column_1, '^\s*0*\.?0*\s*$' );

Output:

COLUMN_1
--------
4.0
200.0
.03

You could also create a user-defined function to convert the value to a number and handle any exceptions in the conversion:

Oracle Setup:

CREATE FUNCTION ValidateNumber(
  value IN VARCHAR2
) RETURN NUMBER
IS
BEGIN
  RETURN TO_NUMBER( value );
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
/

Query:

SELECT column_1
FROM   table_1
WHERE  ValidateNumber( column_1 ) != 0;

Output:

COLUMN_1
--------
4.0
200.0
.03