How do I check if a column is empty or null in MySQL

Question

I have a column in a table which might contain null or empty values  How do I check if a column is empty or null in the rows present in a table    e g  null or    or      or          and

User · Answer

You can test whether a column is null or is not null using WHERE col IS NULL or WHERE col IS NOT NULL e.g.

SELECT myCol 
FROM MyTable 
WHERE MyCol IS NULL

In your example you have various permutations of white space. You can strip white space using TRIM and you can use COALESCE to default a NULL value (COALESCE will return the first non-null value from the values you suppy.

e.g.

SELECT myCol
FROM MyTable
WHERE TRIM(COALESCE(MyCol, '')) = ''

This final query will return rows where MyCol is null or is any length of whitespace.

If you can avoid it, it's better not to have a function on a column in the WHERE clause as it makes it difficult to use an index. If you simply want to check if a column is null or empty, you may be better off doing this:

SELECT myCol
FROM MyTable
WHERE MyCol IS NULL OR MyCol =  ''

See TRIM COALESCE and IS NULL for more info.

Also Working with null values from the MySQL docs

User · Answer

SELECT column name FROM table name WHERE column name IN  NULL

User · Answer

Check for null   column is null isnull  column    Check for empty   column         However  you should always set NOT NULL for column  mysql optimization can handle only one IS NULL level

User · Answer

As defined by the SQL-92 Standard  when comparing two strings of differing widths  the narrower value is right-padded with spaces to make it is same width as the wider value  Therefore  all string values that consist entirely of spaces  including zero spaces  will be deemed to be equal e g             IS TRUE           IS TRUE            IS TRUE                 IS TRUE etc   Therefore  this should work regardless of how many spaces make up the some col value   SELECT      FROM T  WHERE some col IS NULL         OR some col          or more succinctly   SELECT      FROM T  WHERE NULLIF some col       IS NULL

User · Answer

If you want to have NULL values presented last when doing an ORDER BY  try this   SELECT   FROM my table WHERE NULLIF some col      IS NULL

User · Answer

SELECT   FROM tbl WHERE trim IFNULL col       lt  gt

User · Answer

This will select all rows where some col is NULL or     empty string   SELECT   FROM table WHERE some col IS NULL OR some col

User · Answer

select   from table where length RTRIM LTRIM column name     gt  0

User · Answer

You can also do   SELECT   FROM table WHERE column name LIKE      The inverse being  SELECT   FROM table WHERE column name NOT LIKE

User · Answer

try this if the datatype are string and row is null SELECT   FROM table WHERE column name IS NULL OR column name       if the datatype are int or column are 0 then try this SELECT   FROM table WHERE column name  gt    0

User · Answer

Get rows with NULL  0                       SELECT   FROM table WHERE some col IS NOT TRUE   Get rows without NULL  0                       SELECT   FROM table WHERE some col IS TRUE

User · Answer

try  SELECT 0 IS NULL       IS NULL   NULL IS NULL  - gt  0  0  1   or  SELECT ISNULL         ISNULL  NULL    - gt  0  1   Reference

User · Answer

This statement is much cleaner and more readable for me   select   from my table where ISNULL NULLIF some col

User · Answer

Another method without WHERE  try this     Will select both Empty and NULL values   SELECT ISNULL NULLIF fieldname       FROM tablename

User · Answer

While checking null or Empty value for a column in my project  I noticed that there are some support concern in various Databases   Every Database doesn t support TRIM method   Below is the matrix just to understand the supported methods by different databases   The TRIM function in SQL is used to remove specified prefix or suffix from a string  The most common pattern being removed is white spaces  This function is called differently in different databases    MySQL  TRIM    RTRIM    LTRIM   Oracle  RTRIM    LTRIM   SQL Server  RTRIM    LTRIM     How to Check Empty Null  -      Below are two different ways according to different Databases-   The syntax for these trim functions are    Use of Trim to check-   SELECT FirstName FROM UserDetails WHERE TRIM LastName  IS NULL Use of LTRIM  amp  RTRIM to check-   SELECT FirstName FROM UserDetails WHERE LTRIM RTRIM LastName   IS NULL   Above both ways provide same result just use based on your DataBase support  It Just returns the FirstName from UserDetails table if it has an empty LastName  Hoping this will help you

User · Answer

I hate messy fields in my databases  If the column might be a blank string or null  I d rather fix this before doing the select each time  like this   UPDATE MyTable SET MyColumn NULL WHERE MyColumn     SELECT   FROM MyTable WHERE MyColumn IS NULL   This keeps the data tidy  as long as you don t specifically need to differentiate between NULL and empty for some reason

User · Answer

In my case  space was entered in the column during the data import and though it looked like an empty column its length was 1  So first of all I checked the length of the empty looking column using length column  then based on this we can write search query  SELECT   FROM TABLE WHERE LENGHT COLUMN   0

User · Answer

Either   SELECT IF field1 IS NULL or field1        empty   field1  as field1 from tablename   or  SELECT case when field1 IS NULL or field1              then  empty          else field1    end as field1 from tablename

User · Answer

A shorter way to write the condition   WHERE some col  gt       Since null  gt     produces unknown  this has the effect of filtering out both null and empty strings

[mysql] How do I check if a column is empty or null in MySQL?

Examples related to mysql

Examples related to sql