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
167
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;
Baeldung
baeldung.com โบ home โบ sql queries โบ find null or empty values in sql
Find Null or Empty Values in SQL Baeldung on SQL
August 5, 2026 - MS SQL provides a function ISNULL to check for null values: SELECT id, name FROM Department WHERE ISNULL(code, '') = '' OR TRIM(code) = ''; The ISNULL function in MS SQL takes two arguments: the first is a value or column name, and the second ...
Counting rows where ALL columns are either null or empty in the row?
What are the data types? Are they all string types (your example seems to imply that)? Based on https://www.sqlservercentral.com/forums/topic/how-to-get-all-rows-of-a-table-having-null-in-all-columns: You could use something like ISNULL(COALESCE(Col1,Col2,Col3,Col4),'') = '' Is this columns from a single table, or derived? I hope you don't have a table where ALL columns could be null (which would mean it doesn't have a primary key). More on reddit.com
23
4
September 26, 2024How to check isnull or empty in nvarchar column
Hi How to check for isnull or empty? I'm trying to check as below but its not returning expected results. AND (CS.StudentName like '%'+ISNULL(@StudentName,CS.StudentName)+'%') AND (Cl.Street like '%'+ISNULL(@StreetAddress,Cl.Street)+'%') The above statements are ignoring the rows if the column ... More on forums.sqlteam.com
4
0
October 7, 2015Check the Variable is Empty or Null โ SQLServerCentral Forums
Check the Variable is Empty or Null Forum โ Learn more on SQLServerCentral More on sqlservercentral.com
sql server - Test if any columns are NULL - Database Administrators Stack Exchange
I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ... More on dba.stackexchange.com
03:41
SQL Null or Empty How to Check for Null or Empty Column in SQL ...
08:29
SQL to find NULL in any column - YouTube
03:03
How to find null or empty in SQL Server - YouTube
17:05
SQL NULL vs Empty String vs Blank Space (Visually Explained) | ...
17:05
SQL NULL vs Empty String vs Blank Space Explained | #SQL Course ...
W3Schools
w3schools.com โบ sql โบ sql_null_values.asp
SQL NULL Values - IS NULL and IS NOT NULL
SELECT column_names FROM table_name ... a selection from the Customers table used in the examples: The IS NULL operator is used to test for empty values (NULL values)....
GeeksforGeeks
geeksforgeeks.org โบ sql server โบ how-to-check-a-column-is-empty-or-null-in-sql-server
How to Check a Column is Empty or Null in SQL Server - GeeksforGeeks
January 31, 2024 - In SQL Server table columns, there can be times when there is NULL data or Column Value is Empty ('').
Devart
devart.com โบ home โบ how to โบ how to handle null or empty values in sql server
How to Handle Null or Empty Values in SQL Server
December 19, 2024 - We can then combine the IS NULL operator with the search for empty values using the OR operator as below: SELECT column_names FROM table_name WHERE column_name = '' OR column_name IS NULL ยท We want to check whether all products are assigned ...
Reddit
reddit.com โบ r/sqlserver โบ counting rows where all columns are either null or empty in the row?
r/SQLServer on Reddit: Counting rows where ALL columns are either null or empty in the row?
September 26, 2024 -
I'd rather not write a bunch of AND clauses, so is there a quick, efficient way to do this?
I'm importing some data with 10 fields into a SQL Server from a CSV file. Occasionally this file has null/empty values across all the cells/columns.
What I'd like to do is just write one relatively short sql statement to simply count (at first) all these rows. I'd rather do it without doing something like:
...and (column1 is null or column1 = '')
...and (column2 is null or column2 = '')
etc...
Is there a good way to do this, or am I stuck with the above?
Top answer 1 of 13
3
What are the data types? Are they all string types (your example seems to imply that)? Based on https://www.sqlservercentral.com/forums/topic/how-to-get-all-rows-of-a-table-having-null-in-all-columns: You could use something like ISNULL(COALESCE(Col1,Col2,Col3,Col4),'') = '' Is this columns from a single table, or derived? I hope you don't have a table where ALL columns could be null (which would mean it doesn't have a primary key).
2 of 13
3
You might be able to use a combination of NULLIF and COALESCE. On the assumption you want to count null values and blank values, check to see if you get a NULL value using: COALESCE(NULLIF(col1,''),NULLIF(col2,''), etc) The NULLIF should check each column and return NULL if the string is blank. Then the COALESCE should return the first non-NULL value ... you can use ISNULL to check if THAT value is NULL, and if so, count it. There may be a better way to actually write this than what I have above, but you shouldn't need a ton of AND/OR statements. I suspect someone with better SQL-fu than I can give a better example.
SQLTeam
forums.sqlteam.com โบ transact-sql
How to check isnull or empty in nvarchar column - Transact-SQL - SQLTeam.com Forums
October 7, 2015 - Hi How to check for isnull or empty? I'm trying to check as below but its not returning expected results. AND (CS.StudentName like '%'+ISNULL(@StudentName,CS.StudentName)+'%') AND (Cl.Street like '%'+ISNULL(@StreetAddress,Cl.Street)+'%') The above statements are ignoring the rows if the column has explicit NULL value.
Quora
quora.com โบ What-is-the-standard-SQL-query-for-checking-null-or-empty-strings
What is the standard SQL query for checking null or empty strings? - Quora
Answer (1 of 3): In standard SQL, you can use the [code ]IS NULL[/code] predicate to check if a value is [code ]NULL[/code]. To check for empty strings, you can use the [code ]IS NULL[/code] predicate along with the [code ]LENGTH[/code] function (or its equivalent for your database system) to che...
Quora
quora.com โบ How-do-you-check-if-a-column-is-blank-in-SQL
How to check if a column is blank in SQL - Quora
Answer (1 of 3): SELECT * FROM table_name WHERE column_name IS NULL; Or, SELECT * FROM table_name WHERE column_name IS NULL AND column_name LIKE 'โโ; (Note: put begining and ending quotes without any text/ space in 2nd Query. This would fetch all rows with blank/ null values in column ...
SQLServerCentral
sqlservercentral.com โบ home โบ topics
Check the Variable is Empty or Null โ SQLServerCentral Forums
September 9, 2014 - Perhaps internally SQL Server treats ISNULL() like any other scalar function, so it prevents the optimiser from being really efficient and has to do more stuff separately for each row. Sure, but I still prefer a single check. Note that the code should include the comments to keep clarity on what it is doing. WHERE MyNullableCol != '' --This will avoid NULLs and empty strings ยท If you want to do the opposite (look for NULLs and empty strings), you need both conditions.
Mimo
mimo.org โบ glossary โบ sql โบ is-null
SQL IS NULL Condition: Syntax, Usage, and Examples
The SQL IS NULL condition helps you check if a column contains no value, meaning it's undefined or missing. In relational databases, NULL represents the absence of data, not zero or an empty string.
Top answer 1 of 6
20
An extension to @db2's answer with less (read:zero) hand-wrangling:
DECLARE @tb nvarchar(512) = N'dbo.[table]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ N' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + N' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb)
AND is_nullable = 1;
EXEC sys.sp_executesql @sql;
2 of 6
9
You should list out all the columns as per JNK's comment.
WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL
A somewhat less efficient approach that avoids this is below though.
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT *
FROM YourTable AS T1
WHERE (
SELECT T1.*
FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
).exist('//*/@ns:nil') = 1
(Based on this SO answer)
W3Schools
w3schools.com โบ sql โบ func_sqlserver_isnull.asp
SQL Server ISNULL() Function
SQL Examples SQL Editor SQL Quiz SQL Exercises SQL Server SQL Syllabus SQL Study Plan SQL Training ... The ISNULL() function returns a specified value if the expression is NULL.
Red Gate Software
red-gate.com โบ home โบ checking for null with oracle sql
Checking for NULL with Oracle SQL | Simple Talk
July 14, 2021 - The LNNVL function is used in the WHERE clause of an SQL statement when one of the operands may contain a NULL value. The function returns TRUE is the result of the condition is FALSE and FALSE is the result of the condition is TRUE or UNKNOWN.
EnterpriseDB
enterprisedb.com โบ postgres-tutorials โบ how-null-and-empty-strings-are-treated-postgresql-vs-oracle
How NULL and empty strings are treated in PostgreSQL vs Oracle | EDB
This article discusses the differences between how Oracle and PostgreSQL evaluate NULL characters and empty strings. Oracle reads empty strings as NULLs, while PostgreSQL treats them as empty. Concatenating NULL values with non-NULL characters results in that character in Oracle, but NULL in ...
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).
LearnSQL.com
learnsql.com โบ cookbook โบ how-to-find-records-with-null-in-a-column
How to Find Records with NULL in a Column | LearnSQL.com
Use the IS NULL operator in a condition with WHERE to find records with NULL in a column. Of course, you can also use any expression instead of a name of a column and check if it returns NULL.
Stack Abuse
stackabuse.com โบ mysql-check-if-column-is-null
MySQL Check if Column is Null
April 26, 2023 - 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.
Mode
mode.com โบ sql-tutorial โบ sql-is-null โบ index.html
SQL IS NULL | Basic SQL - Mode
May 23, 2016 - Some tables contain cells with no data in them. These are known as null values. You can use SQL's IS NULL operator to select rows that have no data in a given column.
Microsoft Power BI Community
community.powerbi.com โบ t5 โบ Desktop โบ Check-if-a-column-values-is-blank-and-simply-return-Yes-N โบ td-p โบ 1984043
Solved: Check if a column values is blank and simply retur... - Microsoft Power BI Community
October 31, 2021 - Good morning, i looked around and i still couldn't find a solution to something so simple. I have a colmun where some rows have values other don't. I need to create another column that returns Yes if there's a value, No if there's not I tried : if(isblank[columnName]),"True","False") if(isblank[c...