There are many times were we need to handle NULL and "empty" values in SQL Server. Note however, that there is a difference between a NULL and an "empty" value.
In this example we will examine the above cases and ways of handling it, when developing data processes in SQL Server.
Create a Sample Table with Some Data
Consider the following table:
CREATE TABLE [dbo].[table_A] ( [id] [int] NOT NULL, [name] [varchar](50) NULL, CONSTRAINT [PK_table_A] PRIMARY KEY CLUSTERED([id] ASC) ) ON [PRIMARY]; GO
Let's populate the table with some data:
INSERT INTO table_A(id,[name]) VALUES (1,'A'), (2,'B'), (3,''), (4,NULL); GO
Note that the third record (id=3) contains an empty string for the column "name". Also note that the fourth (id=4) record contains a NULL value for the same column.
Strengthen you SQL Server development skills! Enroll to our Online Course!
Check our online course titled "Essential SQL Server Development Tips for SQL Developers" (special limited-time discount included in link).
Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Enroll Now with Discount!
Identifying the Issue with NULL and Empty Values
Let's see how can we handle the two above "special" cases.
First of all if we select all the records from table_A we will get:
select id, name from table_A; GO
Result:
Handling the Issue of NULL and Empty Values
Then let's try to handle the record having the NULL value and set as a new value the string "NewValue" for the result set of our select statement.
SQL Server provides 2 functions for doing this; (i) the ISNULL; and (ii) the COALESCE. Even though the two functions are quite similar, still they have some differences:
(1) ISNULL takes only two parameters as input; (a) the expression to be checked and (b) the replacement value
(2) COALESCE takes N parameters as input (N>=2). By having N expressions as input parameters it returns the first expression that IS NOT NULL. If only all expressions are NULL it then returns a NULL value. It is like a more enhanced version of ISNULL.
Let's try to handle the above scenario with these two functions.
Using ISNULL
--USE of ISNULL SELECT id,ISNULL([name],'NewValue') FROM table_A; GO
Result:
Using COALESCE
--USE of COALESCE SELECT id,COALESCE([name],'NewValue','NewValue2') FROM table_A; GO
Result:
Hmmm, we can see that even though for the record with id=4 we got the "NewValue" string instead of NULL, the record with id=3 still returned an empty string value.
In this case it seems that the ISNULL and COALESCE functions had no effect on the third record's result. This was expected because that record does not contain a NULL value but an empty value instead. An empty value cannot be considered as a NULL of course.
So, how do we deal with this? How can we handle empty string values?
Unfortunately there is not a "magic" formula for that. The only suggestion is not to use empty strings but use NULL values instead which can be handled by the ISNULL, NULLIF and COALESCE functions.
Introducing a Custom Function for Handling Null and Empty Values
Additionally you can always create your own user-defined scalar-valued function that can deal with the issue of the empty string values. In my example, I created the following function and called it IsEmpty:
CREATE FUNCTION isEmpty ( -- Input Parameters @input as varchar(250), @newValue varchar(250) ) -- Output parameter RETURNS varchar (250) AS BEGIN -- First handle the case where the input value is a NULL Declare @inputFiltered as varchar(250) set @inputFiltered=ISNULL(@input,'') -- The main logic goes here RETURN (case rtrim(ltrim(@inputFiltered)) when '' then rtrim(ltrim(@newValue)) else rtrim(ltrim(@inputFiltered)) end) END GO
My function takes as input two parameters; (a) the input string and (b) the replacement string.
Then by using a combination of the ISNULL function and the CASE statement it handles both NULL and EMPTY string values. Though, the above user-defined function just handles strings and not any other expressions.
Now if we try running the IsEmpty function for the same example we get the following:
SELECT id, dbo.isEmpty([name],'NewValue') FROM table_a; GO
Result:
Well, I guess that's it! 🙂
You can find more information regarding the above mentioned built-in SQL Server functions that deal with NULLs on the following MSDN Library links: ISNULL, NULLIF, COALESCE.
Featured Online Courses:
SQL Server Fundamentals – SQL Database for Beginners
Essential SQL Server Administration Tips
Essential SQL Server Development Tips for SQL Developers
SQL Server 2019: What's New – New and Enhanced Features
How to Import and Export Data in SQL Server Databases
Introduction to Azure SQL Database for Beginners
Introduction to Azure Database for MySQL
Working with Python on Windows and SQL Server Databases
Boost SQL Server Database Performance with In-Memory OLTP
Introduction to Computer Programming for Beginners
.NET Programming for Beginners – Windows Forms with C#
Introduction to SQL Server Machine Learning Services
Entity Framework: Getting Started – Complete Beginners Guide
Learn How to Install and Start Using SQL Server in 30 Mins
A Guide on How to Start and Monetize a Successful Blog
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.
Views:11,822
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking "Accept All", you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent. Read More
0 Response to "Sql Continue Query if Result Not is Emptyu"
Post a Comment