Working with functions to Concatenate columns

Pinal Dave, Blogger and Speaker posted about SQL SERVER – Puzzle – Working with functions to Concatenate columns in which he asked to show a method to concatenate columns of different data types that take care of NULL as well

The best way I can think of is to use CONCAT function introduced in version 2012

CREATE TABLE #temp (
emp_name NVARCHAR(200) NOT NULL,
emp_middlename NVARCHAR(200) NULL,
emp_lastname NVARCHAR(200) NOT NULL
);

INSERT INTO #temp VALUES( ‘SQL’, NULL, ‘Authority’ );
INSERT INTO #temp VALUES( ‘SQL’, ‘Server’, ‘Authority’ );

SELECT concat(emp_name ,emp_middlename ,emp_lastname) AS Result
FROM #temp;

DROP TABLE #temp;

The result is

Result
----------------------
SQLAuthority
SQLServerAuthority

Note : CONCAT will also take care different datatypes and you do not need to explicitely convert the values.

SELECT CONCAT(1,'TEST')

The result is 1TEST

GO

Advertisements

2 thoughts on “Working with functions to Concatenate columns

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s