[FIXED] How to cast nulls to blank and those with number to decimal 3 places?

Issue

If I want the output to be blank if there is no gpa, and rounded to 3 decimal places if there is a gpa - how do I get that in one statement?

Here are the 2 statements I have come up with that I am having trouble combining as one (for blank if answer is null and 3 places decimal if there is a gpa).

decimal 3 places:

NULLIF(cast(round(termgpa.gpa, 3) AS DECIMAL(18, 3)), 0)

if null then blank:

isnull(cast(termgpa.gpa as varchar), ' ')

Solution

How about using a case statement. Also, if you want the column to contain both blanks and decimal numbers you will have to cast it as a varchar. The below statement shows both null and non null.

declare @gpa decimal(18,5)

set @gpa = '5.789456'
select 
    case
        when @gpa is null then ''
        else cast(cast(round(@gpa, 3) AS DECIMAL(18, 3)) as varchar)
    end as gpa
--returns 5.789

set @gpa = null
select 
    case
        when @gpa is null then ''
        else cast(cast(round(@gpa, 3) AS DECIMAL(18, 3)) as varchar)
    end as gpa
--returns blank

Answered By - Davin Studer

Answer Checked By - Mary Flores (PHPFixing Volunteer)