How to replace NULL with empty string in SQL?

Question

I am using below query to fetch column value by comma separated.

    (SELECT STUFF ((SELECT  ',' + CAST(Proj_ID AS VARCHAR) FROM PROJECT
    left join dbo.PROJ_STA on
    Project.PROJ_STA_ID = Project.PROJ_STA_ID
    WHERE ENTER_DT < DATEADD(Year, -7, GETDATE())  AND PROJ_LFCYC_STA_CD = 'A' AND 
    PROJ_STA.PROJ_STA_DS = 'Cancelled' FOR XML PATH('')), 1, 1, '') 
    AS Enter_Date)  

Can anyone guide me to replace null value by empty string here.

Updated:

    (SELECT STUFF ((SELECT  ',' + coalesce( CAST(Proj_ID AS VARCHAR), '' ) FROM PROJECT
    left join dbo.PROJ_STA on
    Project.PROJ_STA_ID = Project.PROJ_STA_ID
    WHERE ENTER_DT < DATEADD(Year, -7, GETDATE())  AND PROJ_LFCYC_STA_CD = 'A' AND 
    PROJ_STA.PROJ_STA_DS = 'Cancelled' FOR XML PATH('')), 1, 1, '') 
    AS Enter_Date)  

Answer 1

Try IsNull

select ISNULL(Column,'') as ColumnName

OR COALESCE

select COALESCE(NULLIF(ColumnName,''), 'Column')

Answer 2

An example from the AdventureWorks database

select e.ModifiedDate, ISNULL(p.FirstName,'') as FirstName
from Person.BusinessEntity as e 
    left join Person.Person as p on e.BusinessEntityID = p.BusinessEntityID 

By using this, if there are no matching Person records, the FirstName will be displayed as an empty string instead of NULL

Answer 3

You can white out null values with the coalesce function

select coalesce(MyColumn, '')

Coalesce takes any number of columns or constants and returns the first one which isn't null.

Your query would be:

(SELECT STUFF ((SELECT  ',' + convert(varchar, coalesce( Proj_ID, '' )) FROM PROJECT
left join dbo.PROJ_STA on
Project.PROJ_STA_ID = Project.PROJ_STA_ID
WHERE ENTER_DT < DATEADD(Year, -7, GETDATE())  AND PROJ_LFCYC_STA_CD = 'A' AND 
PROJ_STA.PROJ_STA_DS = 'Cancelled' FOR XML PATH('')), 1, 1, '') 
AS Enter_Date)