Home » Control the Concatenation Results in SQL Server

Control the Concatenation Results in SQL Server

You can control the concatenation results using SQL ServerCONCAT_NULL_YIELDS_NULL(T-SQL). Depending upon the CONCAT_NULL_YIELDS_NULL settings, the concatenation results are treated as null or empty string values. When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. When SET CONCAT_NULL_YIELDS_NULL is OFF then the null value is treated as an empty string. Concatenating a null value with a string yields the string itself.

For Example:

 SET CONCAT_NULL_YIELDS_NULL ONdeclare @firstname varchar(15)declare @lastname varchar(15)select @firstname='Bush'select @lastname=nullselect @firstname+@lastname

Result:

 NULLSET CONCAT_NULL_YIELDS_NULL OFFdeclare @firstname varchar(15)declare @lastname varchar(15)select @firstname='Bush'select @lastname=nullselect @firstname+@lastname

Result:

 Bush

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.