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)

Yes But since i am separatin it by comma, i used it as Varchar — sk7730
– sk7730, Commented Jan 20, 2014 at 12:05

MusicLovingIndianGirl · Accepted Answer · 2014-01-20 12:10:15Z

17

Try IsNull

select ISNULL(Column,'') as ColumnName

OR COALESCE

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

edited Jan 20, 2014 at 12:10

answered Jan 20, 2014 at 11:59

MusicLovingIndianGirl

5,9459 gold badges38 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MusicLovingIndianGirl Over a year ago

Try IFNULL(columnName, '')

Varuna · Accepted Answer · 2015-06-11 04:42:06Z

4

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

answered Jun 11, 2015 at 4:42

Varuna

414 bronze badges

Comments

Liath · Accepted Answer · 2014-01-20 12:09:44Z

2

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)

edited Jan 20, 2014 at 12:09

answered Jan 20, 2014 at 11:58

Liath

10.2k10 gold badges55 silver badges83 bronze badges

6 Comments

sk7730 Over a year ago

Thanks, But i am still getting null value if i use coalsec or isnull in my query.

Liath Over a year ago

If you've already tried with coalesce and IsNull can you show the version with it not working? It's much more likely to be an issue with your query than the functions themselves!

Liath Over a year ago

@saravanakumarr a schema would be handy too!

sk7730 Over a year ago

Sure, pls refer updated question...I am not getting error. I am getting null value again.

sk7730 Over a year ago

Still i am getting Null Value.

|

Collectives™ on Stack Overflow

How to replace NULL with empty string in SQL?

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related