This will select all rows where some_col is NULL or '' (empty string)
SELECT * FROM table WHERE some_col IS NULL OR some_col = '';
Top answer 1 of 16
569
This will select all rows where some_col is NULL or '' (empty string)
SELECT * FROM table WHERE some_col IS NULL OR some_col = '';
2 of 16
166
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;
W3Schools
w3schools.com › mysql › mysql_null_values.asp
MySQL NULL Values - IS NULL and IS NOT NULL
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; Below is a selection from the "Customers" table in the Northwind sample database: The IS NULL operator is used to test for empty values (NULL values).
Stack Abuse
stackabuse.com › mysql-check-if-column-is-null
MySQL Check if Column is Null
April 26, 2023 - SELECT * FROM table_name WHERE column_name IS NOT NULL; This will return all rows from table_name where the value in column_name is not NULL. Note: Keep in mind that NULL is a special value in MySQL that represents the absence of a value. It is not the same as an empty string or the number 0.
GeeksforGeeks
geeksforgeeks.org › mysql › how-to-check-a-column-is-empty-or-null-in-mysql
How to Check a Column is Empty or Null in MySQL? - GeeksforGeeks
February 2, 2024 - To determine if a column is empty or null in MySQL, we utilize the IS NULL and IS NOT NULL conditions in a SELECT statement.
TutorialsPoint
tutorialspoint.com › how-do-i-check-if-a-column-is-empty-or-null-in-mysql
How do I check if a column is empty or null in MySQL?
To check if a column is empty or null , we can use the WHERE clause with IS NULL and for empty we can use the condition ' ' i.e., empty space. The steps required for this are as folllows: First a table is created with the help of CREATE command as follows ? mysql> CREATE table ColumnValueNullDemo ...
TutorialsPoint
tutorialspoint.com › how-to-check-if-field-is-null-or-empty-in-mysql
How to check if field is null or empty in MySQL?
mysql> SELECT IF(Id IS NULL or Id = '', 'NULLId', Id) as UpdatedColumnValue from NullAndEmptyDemo; The following is the output that replace the values, if NULL or empty (“”) is found at first −
Top answer 1 of 10
256
Either use
SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1
from tablename
or use
SELECT case when field1 IS NULL or field1 = '' then 'empty' else field1 end as field1 from tablename
If you only want to check for null and not for empty strings then you can also use ifnull() or coalesce(field1, 'empty'). But that is not suitable for empty strings.
2 of 10
55
Using nullif does the trick:
SELECT ifnull(nullif(field1,''),'empty or null') AS field1
FROM tablename;
How it works: nullif is returning NULL if field is an empty string, otherwise returns the field itself. This has both the cases covered (the case when field is NULL and the case when it's an empty string).
Top answer 1 of 6
47
Does this do what you want?
SELECT *
FROM UserProfile
WHERE PropertydefinitionID in (40, 53)
AND ( PropertyValue is NULL
or PropertyValue = '' );
2 of 6
24
Here's a slightly different way:
SELECT *
FROM UserProfile
WHERE PropertydefinitionID in (40, 53)
AND (LEN(ISNULL(PropertyValue,'')) = 0)
W3Schools
w3schools.com › sql › sql_null_values.asp
SQL NULL Values - IS NULL and IS NOT NULL
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; Below is a selection from the Customers table used in the examples: The IS NULL operator is used to test for empty values (NULL values).
Delft Stack
delftstack.com › home › howto › mysql › mysql check if column is null or empty
How to MySQL Check if Column Is Null or Empty | Delft Stack
February 2, 2024 - FROM table-name [WHERE column-name IS NULL or column-name = ''] In the query above, the basic syntax of Select gets used to form a statement that extracts null and empty values from the table.
Top answer 1 of 13
348
Compare value of phone2 with empty string:
select phone, phone2
from jewishyellow.users
where phone like '813%' and phone2<>''
Note that NULL value is interpreted as false.
2 of 13
54
To check if field is NULL use IS NULL, IS NOT NULL operators.
MySql reference http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
Fedingo
fedingo.com › home › how to check if column is empty or null in mysql
How to Check if Column is Empty or Null in MySQL - Fedingo
June 13, 2024 - Typically, here is the MySQL query developers use to check if column is empty or null in MySQL. mysql> SELECT * FROM table_name WHERE column_name = NULL OR column_name = '';
TechBrothersIT
techbrothersit.com › 2018 › 12 › how-to-check-if-column-is-empty-or-null.html
Welcome To TechBrothersIT: How to check if a column is empty or null in MySQL - MySQL Developer Tutorial
Select * from customer where lastname is null or rtrim(ltrim(lastname))=''; Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest · Note: Only a member of this blog may post a comment. ... Using Union and order by clause in MySQL - MySQL D...
PrintMyFonts
askingbox.com › question › mysql-how-to-check-if-field-is-null-or-empty
MySQL: How to check if Field is NULL or EMPTY
SELECT IF(col IS NULL OR col = '', 'empty', col) FROM tab · With this query, you are checking at each dataset whether "col" is NULL or empty and depending on the result of this condition, either the string "empty" is returned in the case that the condition is TRUE or the content of the column ...
MySQL
dev.mysql.com › doc › en › working-with-null.html
MySQL :: MySQL 8.4 Reference Manual :: 5.3.4.6 Working with NULL ...
To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here: mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+
Folkstalk
folkstalk.com › home › 2022 › september
Mysql Check If Null Or Empty String With Code Examples
September 21, 2022 - The following statement returns no rows, because expr = NULL is never true for any expression: mysql> SELECT * FROM my_table WHERE phone = NULL; To look for NULL values, you must use the IS NULL test.
Dataedo
dataedo.com › kb › query › mysql › check-column-nullable
Check if is column nullable in MySQL database - MySQL Data Dictionary Queries
Article for: MySQL ▾ SQL Server Azure SQL Database Oracle database Snowflake Amazon Redshift IBM Db2 Teradata Vertica PostgreSQL MariaDB · This query returns nullability information of the specified column. select c.table_schema as database_name, c.table_name, c.column_name, case c.is_nullable when 'NO' then 'not nullable' when 'YES' then 'is nullable' end as nullable from information_schema.columns c join information_schema.tables t on c.table_schema = t.table_schema and c.table_name = t.table_name where c.table_schema not in ('mysql', 'sys', 'information_schema', 'performance_schema') and t.table_type = 'BASE TABLE' -- and t.table_schema = 'database_name' -- put your database name here order by t.table_schema, t.table_name, c.column_name;
Top answer 1 of 2
10
You can query the INFORMATION_SCHEMA:
http://dev.mysql.com/doc/refman/5.0/en/columns-table.html
In the INFORMATION_SCHEMA.COLUMNS table there is a IS_NULLABLE column.
You could turn it into a function, I imagine, but I would probably put this logic in an outer part.
2 of 2
4
Cade's answer is correct, but the information directly from the link:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'mytable'
NiceSnippets
nicesnippets.com › blog › how-to-check-if-field-is-null-or-empty-with-if-statement-in-mysql
How To Check If Field is NULL or Empty with If Statement in MySQL?
i will show you use of MySQL check if column is null or empty in case statement.
Studytonight
studytonight.com › post › how-to-check-for-null-value-in-sql-where-clause-in-mysql
How to check for NULL value in SQL WHERE clause in MySQL - Studytonight
June 6, 2023 - You can use IS NOT NULL operator ... SQL query in which we will use IS NOT NULL in the WHERE clause. SELECT * FROM my_table WHERE my_column IS NOT NULL;...