Replace NULL with an empty value

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 NULL
s 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.

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',
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Replace NULL with an empty value

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录