How To Replace Null With Empty String In Sql?

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.P

Solution 1:

Try IsNull

selectISNULL(Column,'') as ColumnName

OR COALESCE

selectCOALESCE(NULLIF(ColumnName,''), 'Column')

Solution 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

Solution 3:

You can white out null values with the coalesce function

selectcoalesce(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
leftjoin 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)