CODEWITHSUNDEEP
mysql
Efox
Efox

Reputation: 691

Check field if empty

Is the query correct if I wanted to check if the field has other characters other than null and empty?

select CASE WHEN description IS NULL THEN 'null'
  WHEN description IS NOT NULL THEN 'not null' ELSE 'something else' END 
  AS 'description'from milestone where name like '%Test%' or name like '%test%';

+-------------+
| description |
+-------------+
| not null    |
+-------------+
1 row in set (0.00 sec)

Upvotes: 12

Views: 55321

Answers (6)

Dojo
Dojo

Reputation: 59

I would create a "stored function" (what in MSSQL is called a user-defined function):

CREATE FUNCTION isNullOrSpaces(s TEXT)
RETURNS BOOLEAN DETERMINISTIC
RETURN (IFNULL(LENGTH(TRIM(s)), 0) = 0);

select 
    isNullOrSpaces(null) 'null', 
    isNullOrSpaces('') 'empty string', 
    isNullOrSpaces('   ') 'spaces',
    isNullOrSpaces('  
        ') 'spaces, tab and newline';

Note that the last case - where the value contains tabs and newlines - returns 0 (FALSE). This is thanks to a bad implementation (IMHO) of the built-in TRIM function, which doesn't remove all whitespace.

I would have preferred to make the function isNullOrWhiteSpace, but since this is good enough for many cases and a pure-SQL implementation of a proper TRIM function will be slow, I figured this will do. If you need to handle all whitespace, consider making what MySQL calls a user-defined function (native function is a better name IMO) - see for instance https://www.codeproject.com/articles/15643/mysql-user-defined-functions.

You may also wish to make a version that returns empty string if the argument is NULL, empty or spaces. This version is mainly useful for the WHERE or HAVING clauses of a query, but the only difficult part of making one that works the same except returning empty string or the original string is to name the function appropriately..! Something like this:

CREATE FUNCTION trimEx(s TEXT)
RETURNS TEXT DETERMINISTIC
RETURN IF(IFNULL(LENGTH(TRIM(s)), 0) = 0, '', TRIM(s));

select 
    trimEx(null) 'null',    
    trimEx('') 'empty string',  
    trimEx('   ') 'spaces', 
    trimEx('  not empty  ') 'contains text';

Upvotes: 0

Ivan Marjanovic
Ivan Marjanovic

Reputation: 1049

Maybe you can try something like this:

select IF(LENGTH(description) > 0,'not null', 'null or empty') from milestone

Upvotes: 4

Sliq
Sliq

Reputation: 16494

Simple IF solution:

IF (my_field = '', "not null", "null")

By the way, I personally like to use it like that (shorthand syntax):

IF (my_field = '', 1, 0)

Upvotes: 14

Pavan G Jakati
Pavan G Jakati

Reputation: 1

// checks if the field not null
where field_name !=' '

Upvotes: -3

Phantasma
Phantasma

Reputation: 9

You can also try to use REGEXP:

// 0 is considered empty
WHERE `field` [NOT] REGEXP '[^0]'

// 0 is not considered empty
WHERE `field` [NOT] REGEXP '[^.]'

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Null and empty means NULL + '' (empty string)?

select CASE WHEN description IS NULL or description = '' THEN 'null or empty'
  ELSE 'not null' END 
  AS 'description'

In your original query, there is no possibility of a third case because IS NULL and IS NOT NULL are complementary, between them they have covered all possibilities.

Also, unless you are using case-sensitive collation (very rare, and never by default unless you specifically nominate one), MySQL is not Oracle - these two queries will work the same:

where name like '%Test%' or name like '%test%'
where name like '%test%'

Because MySQL will match strings case-insensitively

Upvotes: 20

Related Questions