Search code examples
sqloracle-databaseselectreplacetrim

Oracle SQL: Replace all non-numeric values, such as NULL, blank or empty strings

I have a column that is formatted as number but seems to contain some other values as well that are causing trouble with my other functions (Rtrim, Xmlagg, Xmlelement, Extract), leading the whole query to fail (which works well if I take this column out).

Can someone tell me the best way to replace anything in a column that is not a valid number by 0 (or alternatively a placeholder number like 99999) ?

I tried the following but guess this only covers NULL values, not empty strings or blank strings which I think I have to replace first. 

TO_CHAR(NVL(a.mycolumn, 0))

Many thanks in advance,
Mike

Solution

  • In Oracle, you should be able to use TRIM():

    NVL(TRIM(a.mycolumn), '0')

    Oracle (by default) treats empty strings as NULL.

    Do note that this will trim the result, even if it is not NULL. If that is not desirable, use CASE:

    (CASE WHEN TRIM(a.mycolumn) IS NULL THEN '0' ELSE a.myolumn END)