How to check null values in multiple fields in where clause mysql

Question

I am trying to match null values in multiple fields in table using where clause, for example

 SELECT * FROM manage_users WHERE coalesce(surname, firstname, lastname, physical_address, postal_address, telephone_number, fax_number, cell_number, email,medical_aid_fund, medical_aid_number, allergies, emergency_contatct_person, emergency_contatct_number, id_number,gender, age_on_raceday, age_groups, shirt_size, type_of_club, roag_membership_number, csa_licence_number, provinical_running_licence_number, provinical_running_canoeing_number, tsa_licence_number, licence_number, number_of_comrade_marathon_completed, number_of_two_oceans_completed,    number_of_duzi_completed, number_of_nonstop_duzi_completd,number_of_amashova_completed, 
number_of_argus_completed, number_of_947_completed, number_of_midmar_mile_completed, make_of_running_shoes, make_of_road_bike, make_of_mtb, make_of_wetsuit) is NOT NULL and id='16'

I have used coalesce for this, is it a right way. I am not getting the expected output using this. Can anyone help me getting the result. It would be greatly appreciated.

What is the expect result? Try just running SELECT * FROM manage_users where id = '16' and see what nulls you have. Is the id field an integer or string type? — andrewb
– andrewb, Commented Jul 30, 2013 at 6:45
MySQL coalesce() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values. — Goutam Pal
– Goutam Pal, Commented Jul 30, 2013 at 6:45
I want get the values from the table which should contain values in the mentioned fields(i mean the mentioned fields should not be null) — Manigandan Arjunan
– Manigandan Arjunan, Commented Jul 30, 2013 at 6:55

Barmar · Accepted Answer · 2013-07-30 07:16:15Z

4

COALESCE returns the first non-null value in the argument list. So your query will succeed if any of the fields are not null, it doesn't require them all to be non-null.

You can instead use:

SELECT * FROM manage_users WHERE CONCAT(<list of columns>) is NOT NULL and id='16'

CONCAT(), like most ordinary functions, returns NULL if any of the arguments are NULL.

To check for any empty values, add:

AND LEAST(<list of string columns>) != ''

This should just be the string columns; if you mix strings and numbers in LEAST, it converts all the strings to numbers, and any string that doesn't begin with a number will be converted to 0, so it won't do a proper test.

edited Jul 30, 2013 at 7:16

answered Jul 30, 2013 at 6:47

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

Manigandan Arjunan Over a year ago

i also want to check empty values

Saranya Sadhasivam · Accepted Answer · 2013-07-30 07:50:34Z

1

Query which will not accept null and empty values. It will select the row in which the mentioned where columns have value in it.

Select * from manage_users where (surname and firstname and lastname and physical_address and postal_address) > ''

answered Jul 30, 2013 at 7:50

Saranya Sadhasivam

1,3147 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to check null values in multiple fields in where clause mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related