Clever Geek Handbook

In Oracle SQL, how do I add text to a column only if it is not null?

I have a column in my query like this:

ROUND(( xyz.DATE - abc.DATE), 5)|| ' days' AS "Average SOD Approval duration"

The problem is when my data is zero, I get "days" printed in every zero field. I want nothing in the output if it was null. and just add "days" when there is data.

+3


source to share


4 answers


Here's a shorter way:

select ltrim(round(d1-d2, 5)|| ' days', ' days') dt from test

SQLFiddle



Edit: If at least one of the dates is zero, the date difference is also zero. After concatenating "days", you only get that string as a value, so truncating the phrase "days" on the left gives null. If the dates do not have zero clipping on the left, this has no effect. Ltrim () function documentation . In this case, with trimming, you can shorten the syntax and avoid re-computation.

+4


source


I suggest NVL2

insteadcase 



SELECT nvl2(ROUND((xyz.DATE - abc.DATE), 5),
            ROUND((xyz.DATE - abc.DATE), 5) || 'days',
            NULL) AS "Average SOD Approval duration"
FROM dual;
+3


source


Use operator CASE 

SELECT CASE WHEN ROUND(( xyz.DATE - abc.DATE), 5) IS NULL 
            THEN NULL 
            ELSE ROUND(( xyz.DATE - abc.DATE), 5)|| ' days'
            END AS "Average SOD Approval duration"
FROM yourtablename
+2


source


Use a case statement:

CASE WHEN ROUND((xyz.date - abc.date), 5) IS NOT NULL 
          THEN ROUND((xyz.date    - abc.date), 5)||' days'
END

You can add an "ELSE NULL" clause to make it doubly clear that if the conditions are not met then null will be returned if you want to make the case statement particularly explicit, but since this is the default, this is not necessary.

+1


source






More articles:


All Articles