How to find empty or NULL column value in a table?

Question

I have a table where I need to find and update the status id if the specific columns are empty or NULL.

I need to check columns Like Phone and Email if its value is empty or NULL, the Status should be updated to 2.

I tried this query:

select * from UserTbl where NULL in(Email, Phone)

As the column values are empty, I am not getting any result.

What is your DBMS product? Oracle? Mysql? Postgres? SQL Server? — Ankit Bajpai, Aug 17 '21 at 09:21

Alexander van Oostenrijk · Answer 1 · 2021-08-17T09:31:20.887

0

This would do it:

UPDATE UserTbl
SET StatusId=2 
WHERE 
      (ISNULL(Phone) OR Phone='')
  AND (ISNULL(Email) OR Email='');

edited Aug 17 '21 at 09:31

answered Aug 17 '21 at 09:21

Is there any other way because there might be More columns to check the Values, Like 15 to 20 columns. in that case this query will be biggy. – PKJI Aug 17 '21 at 09:24
Add them to the `WHERE` clause? Maybe create a stored function that checks whether a column is either NULL or equal to the empty string? – Alexander van Oostenrijk Aug 17 '21 at 09:24
Your Code should be: UPDATE UserTbl SET StatusId=2 WHERE (Phone is NULL OR Phone=' ') AND (Email) is NULL OR Email=' ') – Faojul Ahsan Aug 17 '21 at 09:29
Yes, reading the question carefully I think so too. Updated. – Alexander van Oostenrijk Aug 17 '21 at 09:31

Dhilip H · Answer 2 · 2021-08-17T09:46:17.867

0

Try this -

UPDATE <TableName> 
SET StatusId = 2
WHERE (ISNULL(Phone) OR Phone = '') AND (ISNULL(Email) OR Email= '')

edited Aug 17 '21 at 09:46

answered Aug 17 '21 at 09:23

Dhilip H

Is there any other way because there might be More columns to check the Values, Like 15 to 20 columns. in that case, – PKJI Aug 17 '21 at 09:28
@PKJI Why cant you copy-paste it 15-20 times? – Salman A Aug 17 '21 at 09:35

2 Answers2