How to Replace null with value when null

Question

I have a join query, I want Replace a NULL value with a string if is null. If it is not NULL, replace with a null string.

select distinct Code= R_Schedule.FoodId,Name= FoodName + ' = price ' +(CONVERT( varchar(50),R_Foods.FoodPrice) + ISNULL(str(R_Schedule.dayId ),' - public-'))    from R_Foods join R_Schedule on R_Foods.FoodId=R_Schedule.FoodId where R_Schedule.DayId=6 or DayId is null

please this this Image : https://i.sstatic.net/Kv81f.png

Important section of my query is :

 ISNULL(str(R_Schedule.dayId ),' - public -'))

This section if it is null return 'public' else if it is not null return column's id. I don't want return id if it is not null.

which database you are using ? coz its DB specific

Brave Soul
– Brave Soul

2015-03-02 06:45:32 +00:00
Commented Mar 2, 2015 at 6:45 — Brave Soul
– Brave Soul, Commented Mar 2, 2015 at 6:45
Case When Then would work

Jenish Rabadiya
– Jenish Rabadiya

2015-03-02 06:46:21 +00:00
Commented Mar 2, 2015 at 6:46 — Jenish Rabadiya
– Jenish Rabadiya, Commented Mar 2, 2015 at 6:46
@JenishRabadiya Can you post your answer .please

Psar Tak
– Psar Tak

2015-03-02 06:46:56 +00:00
Commented Mar 2, 2015 at 6:46 — Psar Tak
– Psar Tak, Commented Mar 2, 2015 at 6:46
@JenishRabadiya I don't know how work with case .

Psar Tak
– Psar Tak

2015-03-02 06:50:18 +00:00
Commented Mar 2, 2015 at 6:50 — Psar Tak
– Psar Tak, Commented Mar 2, 2015 at 6:50
@PsarTak can you please post your table schema?

SelvaS
– SelvaS

2015-03-02 06:52:08 +00:00
Commented Mar 2, 2015 at 6:52 — SelvaS
– SelvaS, Commented Mar 2, 2015 at 6:52

Mahesh · Accepted Answer · 2015-03-02 06:52:46Z

2

You can use the CASE WHEN. When dayId is null then it will return - public- Else it will concat with '' Empty string which you can use the other value to set if you want.

select distinct 
   Code = R_Schedule.FoodId,
   Name = CASE WHEN R_Schedule.dayId IS NULL THEN  FoodName + ' = price ' +(CONVERT( varchar(50),R_Foods.FoodPrice)) + ' - public-'
              ELSE  FoodName + ' = price ' +(CONVERT( varchar(50),R_Foods.FoodPrice)) + 'othervalue' END
from 
   R_Foods join R_Schedule 
      on R_Foods.FoodId=R_Schedule.FoodId 
where 
     R_Schedule.DayId=6 
     or DayId is null

answered Mar 2, 2015 at 6:52

Mahesh

8,9422 gold badges37 silver badges55 bronze badges

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

Collectives™ on Stack Overflow

How to Replace null with value when null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related