Welcome to Appsloveworld Technologies, In this session, we learn about the different ways that are available to replace Null values and SQL server. Specifically will be looking at ISNULL() function, case statement, and COALESCE() function.
Now I have a TblEmployeeManager which has got three columns, EmpId, name, and Manager_Id.
Now if I ask you who is the manager of Jackson, you’ll look at the Manager_Id column. Manager_Id for Jackson’s 6 and you look up 6 within the EmpId column,6 is Gabriel. So Jackson’s manager is Gabriel.
So along the same lines, if you look at Anthony, Manager_Id value is NULL, which means Anthony doesn’t have a manager.
And if you remember in our SQL joins series we have written a left outer self join to retrieve employee names along with their manager names.Since Anthony doesn’t have a manager ID, his manager’s name is Null.
Any employee, if he doesn’t have a manager, doesn’t display Null replace those null values with this no manager word. Is this possible? Absolutely Yes, We have different ways to do that.
I don’t want null to be displayed instead of null, I want to replace that with ‘Super Boss’.
We can make use of ISNULL(),case statement and COALESCE() functions. Let us look at practical examples of using each one of them.
All right. So since we are using this Manager_Id column and then looking up in the EmpId column, we’re kind of doing a selfie join here.
And if you’re not sure about what a self-join is, please read the below post.
So let’s right the left outer self join Query.
SELECT Emp.EmployeeName as Employee, Mng.EmployeeName as Manager FROM TblEmployeeManager Emp LEFT JOIN TblEmployeeManager Mng ON Emp.Manager_Id = Mng.EmpId
when we execute the above query, we get the below result.But what’s our ultimate aim? Our ultimate aim is basically to replace the null value with the ‘Super Boss’ word.
So how do we do that? the easiest way to do it is basically to use ISNULL() function.
SELECT Emp.EmployeeName as Employee, ISNULL(Mng.EmployeeName,' Super Boss') as Manager FROM TblEmployeeManager Emp LEFT JOIN TblEmployeeManager Mng ON Emp.Manager_Id = Mng.EmpId
Now ISNULL() is a simple function, now as the name itself suggests ISNULL if it is NULL.
The first parameter, it’s an expression that you pass in. If the expression that you’re passing in, if it returns null, then there is a second parameter called replacement value.
Along the same lines, you can actually use COALESCE() function as well. So another way to replace the null value is to use COALESCE() function
if you look up the definition of COALESCE() function in MSDN books online, it says it returns the first non-null value. What do we mean by that? the usage of the COALESCE() function is pretty much similar to the ISNULL() function, but there is much more to it.
it is basically used to return the first non-null value, very powerful function.you can read more about COALESCE() in the below post.
SELECT Emp.EmployeeName as Employee, COALESCE(Mng.EmployeeName,' Super Boss') as Manager FROM TblEmployeeManager Emp LEFT JOIN TblEmployeeManager Mng ON Emp.Manager_Id = Mng.EmpId
COALESCE() is much more powerful than ISNULL(). You can pass and multiple expressions to that and it returns the first non-null expression value.
The final way to use the case statement. now you have a case statement is also very simple,
SELECT Emp.EmployeeName as Employee, CASE WHEN Mng.EmployeeName IS NULL then 'Supper Boss' ELSE Mng.EmployeeName END as Manager FROM TblEmployeeManager Emp LEFT JOIN TblEmployeeManager Mng ON Emp.Manager_Id = Mng.EmpId
Table Sql Script
GO /****** Object: Table [dbo].[TblEmployeeManager] Script Date: 10/12/2020 9:41:49 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TblEmployeeManager]( [EmpId] [int] IDENTITY(1,1) NOT NULL, [EmployeeName] [nvarchar](max) NULL, [Manager_Id] [int] NULL, CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED ( [EmpId] ASC ) ) GO SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7) GO INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7) GO SET IDENTITY_INSERT [dbo].[TblEmployeeManager] OFF GO
The post Replacing NULL and Empty string in Select statement appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Trouble combining rows into one column using CAST(
- SQL Server: How to get a subitem of sp_helplanguage?
- Script that pull the names of all the stored procedures that has functions in the where clause?
- Index seek with coalesce
- SQL Server : multiple foreign keys from first table linking to the primary key of second table
- Convert timestamp into datetime and save to replace timestamp
- Using If Statement within SQL query
- MSSQL retrieve a value 1 time in a GROUP BY
- Max rows in SQL table where PK is INT 32 when seed starts at max negative value?
- How to convert Oracle TO_NUMBER to SQL Server
- How to set autoLoadEntities: true in connecting Nest js with typeorm
- value comparison on sql server 2008
- Disabling non-clustered index on busy production database fails: Lock request time out period exceeded (Error: 1222)
- Is there a way in SQL Server Management Studio to view field data in a multi-line editable field?
- "metadata could not be determined" when importing with bcp from command line
- Offline database solution for sql server
- Transactions within loop within stored procedure
- Group By by hiding a column - TSQL
- SQL using a lot of CPU
- Building list of items of different types in database
- Is there any way to reference CDC objects in a SSDT database project?
- SQL Server : window function to avoid duplicate rows
- how to restore very large .bak file (180 GB) in SQL Server 2008
- Foreign key relationship betwwen three tables
- How to detect a SQL Server database's read-only status using T-SQL?
- Are these cross joins causing me problems?
- converting Epoch timestamp to sql server(human readable format)
- SQL Select latest post by postID
- Undetermined sort order when using ROW_NUMBER() OVER decimal column with ties
- Pivot table without knowing row values
- Set variable using CASE expression
- SQL Server 2008 database copy - file permissions
- How will you find the sum of numbers in between two numbers.
- I want to assign the number of rows to "variable"
- How to do Data Binding in Gridview?
- How to prevent timeout in query?
- Can I Insert multiple rows based on a query in SQL Server
- Creating indexed view that compiles automatically on certain time
- sqlerrorColumn 'tableName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
- colmn_name UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
- group by clause help
- Count occurrences of a character in sql
- Multiple INSERT statements vs. single INSERT with multiple VALUES
- How to use the PIVOT or UNPIVOT T-SQL syntax to achieve this output?
- Backupset server name on dev server shows Prod server name
- SELECT FROM OPENROWSET( BULK...) changing special characters
- Best approach for high-read table where query depends on computed values
- Codeigniter login system with session to redirect user to page if password correct
- T-SQL generate XML
- Using decimal to binary conversion in SQL Server stored procedure
- TSQL Count(Id) performance with where clause
- Logging to windows application event log
- Why are temporary tables faster than table variables for joins?
- React Project - Request to ExpressJS Server returns 400 - Bad Request
- Creating an Index to optimize MS SQL Query
- Dynamic scheduling of data driven subscription
- How to search on levelOrder values un SQL?
- DataTable: is deleting old DataRows before inserting new safe?
- Disappearing Stored Procedure
- How can I display NULL values in formatted datetimeoffset column?
- Database design - how can I have a recurring database entry?
- Creation of view with multiple subqueries
- Group By and Counting inside group
- What is the best way to implement this SQL query?
- Do unselected columns in a SQL Table slow down a query?
- Track Views on SQL Server
- Sort Gridview based on specific column
- SQL Server Case Statement
- Transpose Keep order
- SQL Server 2012 - updating with a twist
- SQL Server Profiler - Evaluating Reads. What is considered 'good' or 'bad'?
- Alternative negative and positive values
- Find a string by searching all stored procs in SQL Server Management Studio 2008
- How to get records from different tables and ignoring duplicated dates in SQL?
- Debugging in SSIS
- Use table variable in control and data flow
- How can I SUM values that don't have a common GROUP BY condition?
- Cannot connect to SQL Server from a Docker container
- Calculate a Simple Moving Average Crossover using SQL Server 2014
- Datatype not supported in SQL Server 2008 while creating trigger
- Insert stored procedure data in a temp table in SQL Server
- SQL inner join usage
- The parameterized query expects the parameter which was not supplied
- SQL Query - Outstanding Payments
- Performance difference if I UNION first, then put WHERE on unioned result set?
- Estimated Subtree Cost Wildly Off, Terrible Optimization
- EF Core Database First HasNoKey
- Copy a subset of data from remote database to local SQL Server
- How do you select Column Names and grab the data for SQL Server?
- SQL Query help getting monthly values
- Sorting with SQL
- How can i avoid an extra inner join to optimize this query?
- Importing user-friendly Excel sheet into a SQL Server table that has many foreign keys
- Convert UTC Time to Local time in SQL Server 2014 When Using Aggregate Function
- Does PreparedStatement only make temporary changes in database or can I make it permanent?
- Highlight Duplicate Values in a NetSuite Saved Search
- SQL Query Between Dates AND Based on Employee Name
- "The given ColumnMapping does not match up with any column in the source or destination" when uploading csv to sql server
- Structure of T-SQL tables
- Need assistance with querying SQL Server
- Database Connector Error
- SQL Date Range only show data for the last hour
- OLEDB security exception
- SqlDataSource Parameter Problem - Using loginName1 as sql parameter
- Calculating expired vouchers, given a fixed window
- How to increment an alphanumeric ID through code in C#
- Parallel calls to dbcontext in .NET Core EF core
- Simple T-SQL Join question
- SQL Server ORDER BY [aggregation] DESC/ASC
- How to ensure column name does not match with any SQL Server reserved word
- NHibernate uses wrong column type for LINQ contains query (varchar to nvarchar)
- SQL Server Insert Example
- Select number groups in query
- How do I autogenerate confirmation number in SQLServer?
- SQL Server : how to join this?
- SQL Server 2014 restore backup failed
- Cursors in SQL Server: High Performance Penalty
- Using a stored procedure to (+1 or -1) a value from a table
- compare current datetime between 2 date
- How to send and receive parameters to/from SQL Server stored procedure
- Executed SQL-Statement can't be viewed in SQL Server Profiler
- Is it possible to get the client process ID of an application that runs on SQL server?
- Selective Xml Index causing Insert Error: string or binary data would be truncated. the statement has been terminated
- application timeout in 120 - 130 seconds aspnet mvc
- Parallel.ForEach used with NHibernate resulting in SQL Server locks
- Create new sql login by passing encrypted password
- Create SQL Server 2008 Backup from 2008 R2
- How to insert images into SQL Server database table
- Update Foreign Key when copying records to a different table
- Change the Value on Duplicate Rows
- WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value
- What's the right way to compare an NTEXT column with a constant value?
- SQL: time of interval within a defined interval
- BigQuery - How to create a new column where the calculation includes the new column itself?
- Updating the DataGridView with an IF statement of another form (VB.NET)
- SQL joining 2 queries that share a column
- SQL Strange Error converting data type nvarchar to numeric Error. Error only in Where Clause
- SQL Server: stored procedure using recursive CTE finding values matching a total
- Retrieve ID of deleted Database row in C#
- Join table index performance improvements concerns
- Query for the creation date of indexes
- "Invalid object name" error caused by letter cases in table name
- Is It possible to get JSON as an out parameters using sp_executesql
- Query WSUS Database For Required Updates Per Server
- Query containing Logic
- How to create 20 databases at a time in sqlserver?
- Cannot add new column to SQL Server table in Management Studio
- How can I know when SQL Server Full Text Search has indexed a record?
- VB.NET - SQLCommand.ExecScalar() throws unreferenced exception on Return?
- SSIS script task to collect lineage IDs