SQL query to find values without NULL

Question

I have doubt on my SQL.

Please find the query

select a.id, a.phone, b.list,c.ctitle,l,
       if(last_id!=0 and date(activity)=date(start_date), 
       CONCAT(ag.firstname,' ‘,ag.lastname) ,’')
from table name a 
left join <all other tables>
where a.cid = '100' and a.date between '2013-05-01' and '2015-06-14';

The output is showing properly.currently i am getting firstname and lastname. But for some entries first name and last name empty. I need to modify the query so that i can print the non-null value of the column if(last_id!=0 and date(activity)=date(start_date), CONCAT(ag.firstname,' ‘,ag.lastname) ,’') ie same query excluding the null entries for firstname and last name. I tried IS NOT NULL but not working.

Please help

Add to the where clause AND ag.firstname IS NOT NULL AND ag.lastname IS NOT NULL — RiggsFolly
– RiggsFolly, Commented Jun 15, 2017 at 0:10

Gordon Linoff · Accepted Answer · 2017-06-15 00:08:16Z

4

If I understand correctly, you have a problem with NULLs. One simple solution is to use concat_ws():

   concat_ws(' ', ag.firstname, ag.lastname)

If either name is NULL, then it is just ignored in the concatenation.

answered Jun 15, 2017 at 0:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

ardalan ebrahimi · Accepted Answer · 2017-06-15 05:12:59Z

0

I guess you can find the answer here :MySQL CONCAT returns NULL if any field contain NULL

also you can use IFNull syntax for this:

CONCAT(IFNull(ag.firstname,''),IFNull(ag.lastname,''))

answered Jun 15, 2017 at 5:12

ardalan ebrahimi

112 bronze badges

Collectives™ on Stack Overflow

SQL query to find values without NULL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related