Is null or empty mysql?

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 ...)

asked Dec 12, 2011 at 6:49

3

This will select all rows where some_col is NULL or '' (empty string)

SELECT * FROM table WHERE some_col IS NULL OR some_col = '';

answered Dec 12, 2011 at 6:54

mačekmaček

74.1k36 gold badges164 silver badges196 bronze badges

2

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;

answered Dec 12, 2011 at 12:28

onedaywhenonedaywhen

53.6k12 gold badges96 silver badges135 bronze badges

3

A shorter way to write the condition:

WHERE some_col > ''

Since null > '' produces unknown, this has the effect of filtering out both null and empty strings.

answered Jun 30, 2013 at 16:24

AndomarAndomar

227k45 gold badges368 silver badges392 bronze badges

6

Please mind: the best practice it at the end of the 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

answered Dec 12, 2011 at 6:52

Code MagicianCode Magician

22.5k6 gold badges59 silver badges77 bronze badges

2

Another method without WHERE, try this..

Will select both Empty and NULL values

SELECT ISNULL(NULLIF(fieldname,''))  FROM tablename

answered Jan 3, 2016 at 17:06

PodTech.ioPodTech.io

4,46834 silver badges24 bronze badges

2

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

answered Jun 7, 2017 at 3:48

This statement is much cleaner and more readable for me:

select * from my_table where ISNULL(NULLIF(some_col, ''));

answered May 30, 2016 at 16:10

AmaynutAmaynut

3,9115 gold badges38 silver badges43 bronze badges

try

SELECT 0 IS NULL ,  '' IS NULL , NULL IS NULL

-> 0, 0, 1

or

SELECT ISNULL('  ') , ISNULL( NULL )
 -> 0 ,1

Reference

answered Dec 12, 2011 at 6:53

diEchodiEcho

52.6k41 gold badges172 silver badges242 bronze badges

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.

answered Dec 3, 2014 at 17:16

1

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:

1. Use of Trim to check-

   SELECT FirstName FROM UserDetails WHERE TRIM(LastName) IS NULL

2. Use of LTRIM & 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 :)

answered Oct 21, 2016 at 20:58

Vikash PandeyVikash Pandey

5,3036 gold badges39 silver badges42 bronze badges

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;

answered Dec 12, 2011 at 6:52

GhostmanGhostman

5,9589 gold badges33 silver badges52 bronze badges

0

You can also do

SELECT * FROM table WHERE column_name LIKE ''

The inverse being

SELECT * FROM table WHERE column_name NOT LIKE ''

answered Jun 22, 2017 at 3:18

brenjtbrenjt

15.7k13 gold badges78 silver badges116 bronze badges

2

SELECT * FROM tbl WHERE trim(IFNULL(col,'')) <> '';

Paul Roub

36k27 gold badges80 silver badges88 bronze badges

answered Feb 12, 2019 at 15:47

3

My two cents.

In MySQL you can use the COALESCE function:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

So you can simplify your query like this:

SELECT * FROM table WHERE COALESCE(some_col, '') = '';

answered Sep 8, 2021 at 9:54

PiozPioz

5,7353 gold badges45 silver badges60 bronze badges

select * from table where length(RTRIM(LTRIM(column_name))) > 0

answered Jan 25, 2019 at 13:15

Hakan IlgarHakan Ilgar

1415 silver badges14 bronze badges

2

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;

answered Feb 20, 2019 at 15:46

MR ANDMR AND

3365 silver badges26 bronze badges

1

The below SQL query works fine.

SELECT * FROM  WHERE  IS NULL;

answered Mar 23 at 13:55

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 > = 0

answered Mar 31, 2017 at 3:00

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;

answered Sep 23, 2020 at 19:41

ArthurArthur

1172 bronze badges

1

SELECT column_name FROM table_name WHERE column_name IN (NULL, '')

Nathan Tuggy

2,23627 gold badges29 silver badges38 bronze badges

answered Jun 7, 2017 at 2:59

2

Is NULL or empty in MySQL?

Is NULL or empty SQL?

Is NULL 0 or blank?

Is NULL or NULL MySQL?