Replace NULL Values With a Blank Value

Question

I have the following SQL server query:

SELECT  COL.FullName AS 'Name', 
    COB.Reference AS 'Reference',
    COL.WordProcessorText AS 'Template Text',
    CASE COB.Available 
        WHEN 1 THEN 'TRUE'
        WHEN 0 THEN 'FALSE'
    END AS 'Available For Selection',
    COL.HelpText AS 'Help Text',
    CASE COB.DisplayAlert 
        WHEN 1 THEN 'TRUE'
        WHEN 0 THEN 'FALSE'
    END AS 'Display Alert',
    COL.AlertMessage AS 'Alert Text',
    COB.ParentIdLevel1 AS 'Parent Option',
    COB.ParentIdLevel2 AS 'Parent Option 1',
    COB.ParentIdLevel3 AS 'Parent Option 2',
    COB.ParentIdLevel4 AS 'Parent Option 3',
    FL.Name AS 'Category Name',
    EL.SingularText AS 'Entity'
FROM dbo.rCategoryOptionLiteral AS COL
INNER JOIN dbo.CategoryOptionBase AS COB ON COB.Id = COL.ObjectId AND COL.Locale = @Locale
INNER JOIN dbo.FieldLiteral AS FL ON FL.ObjectId = COB.FieldId AND FL.Locale = @Locale
INNER JOIN dbo.FieldBase AS FB ON FB.Id = FL.ObjectId
INNER JOIN dbo.EntityBase AS EB ON EB.Id = FB.EntityId
INNER JOIN dbo.EntityLiteral AS EL ON EL.ObjectId = EB.Id AND EL.Locale = @Locale
WHERE COB.FieldId = @FieldId
  AND COB.ParentOptionId IS NULL;

My issue is that the data in the four ParentIdLevel columns can be NULL and essentially I need to display the NULLs as blanks in the output. However, as the ParentIdLevel columns are of type unqiueidentifier in my CategoryOptionBase table, I am having difficulty displaying these as a blank if they are NULL. I have tried the COALESE function but this hasn't worked for me.

just a note, don't put spaces in alias names for the columns. If you really don't want to remove them, consider using [square brackets] around the names instead of quotes. `SELECT 1 AS [Parent Option]`. See [this](http://stackoverflow.com/questions/10920671/how-do-you-deal-with-blank-spaces-in-column-names-in-sql-server). — Tanner, Apr 05 '17 at 15:30
SQL Server has the IsNull function that might help you here. `IsNull(COB.ParentIdLevel1, ' ') AS 'Parent Option',` — No Refunds No Returns, Apr 05 '17 at 15:30
When I use the `ISNULL` function I get the following error: `Conversion failed when converting from a character string to uniqueidentifier.` — bez91, Apr 05 '17 at 15:40

score 3 · Accepted Answer · answered Apr 05 '17 at 15:40

You have to convert uniqueidentifier to string type.

isnull(convert(char(36),COB.ParentIdLevel1),'') as 'Parent Option',
isnull(convert(char(36),COB.ParentIdLevel2),'') as 'Parent Option 1',
isnull(convert(char(36),COB.ParentIdLevel3),'') as 'Parent Option 2',
isnull(convert(char(36),COB.ParentIdLevel4),'') as 'Parent Option 3',

score 1 · Answer 2 · edited May 23 '17 at 12:02

The replacement value needs to be of the same datatype as the column. If you want to return empty strings for null values for a non-character datatype, you can convert the column to a character datatype inside the function.

You can use isnull() or coalesce() in sql server.

select isnull(convert(varchar(36),col),'') as ...

This will return an empty string if col is null.

or

select coalesce(convert(varchar(36),col),'') as ...

This will also return an empty string if col is null.

The main difference between the two is that coalesce() can support more than 2 parameters, and it selects the first one that is not null. More differences between the two are answered here.

select coalesce(col,col2,'')

coalesce() is also standard ANSI sql, so it is available in most RDBMS, whereas isnull() is specific to sql server.

Reference:

Replace NULL Values With a Blank Value

2 Answers2