Oracle SQL - Find the MIN value of a dataset, but exclude a value in the MIN calculation

Oracle version : 10g

I'm trying to use the MIN function to find the minimum/lowest value of a dataset. The column I'm performing MIN on is a VARCHAR2 column. This column will either contain a numeric value, or a value of '--' which represents that the value is not applicable.

When performing the MIN() function on the column, the '--' is always returned.

I want to find a way to exclude the '--' from being calculated in the MIN statement. I can't use the WHERE clause in the statement t开发者_如何转开发o filter out columns with a '--' because that would exclude valid data.

Note: This is a huge legacy query (500+ lines) so re-writing this massive query is not really an option.

---

MIN(CASE WHEN ColX = '--' THEN NULL ELSE ColX END)

This is valid in SQL Server and I think will probably work on Oracle as well.

As a side note, never ever store numeric data in string fields.

It's inefficient from a space perspective and you will get some weird results when doing inequality comparisons.

If you have these rows in your table:

010
009
1

The MAX value will be 1 and the MIN value will be 009

---

Really you just want the aggregate function to avoid this value. Aggregates skip nulls, so transform the value in question into a null:

min(case your_field
    when '--' then null
    else your_field
    end)

Also, as @JNK points out, storing numbers as strings is fraught with peril. For instance, unless your numbers are all padded to the same length, min will probably give you the wrong result: as strings, '1000' is less than '2'. You can use the float or int functions to convert your strings to actual numbers, but if you have any other non-numeric characters in there then the SQL will throw an error.