Learn more about SQL Server tools


Latest from MSSQLTips

Concatenation of Different SQL Server Data Types

MSSQLTips author Chad Churchwell By:   |   Read Comments (7)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Functions - System

Is there a way to concatenate different data types in TSQL that does not require me to convert everything to the same data type.


TSQL provides 2 ways to concatenate data, the + sign and the new CONCAT() function. This tip will cover the differences in the two, so you can achieve the expected behavior in your code.

The way most us are used to concatenating data together is using the + sign. The problem we run into using this method is it expects the different items being concatenated together to be of the same data type. You have to perform an explicit conversion if they are not the same data type. The following example is trying to concatenate a INT and a String together, you can see SQL cannot handle the different data types.

INT String Error

The new CONCAT() function does an implicit conversion to string for all items being concatenated together. The other way, which requires more coding is to perform an explicit conversion and use the + method for concatenation. Below is an example of both methods where the result returns without errors. The first PRINT statement uses CONCAT() where an implicit conversion is performed and everything is converted to a string and then concatenated together. The second PRINT uses the + method for concatenation where you have to explicitly convert the INT to a String to get it to return without error.

INT String Success

The next example will be showing DATETIME concatenated with a String. This screenshot is using the + method for concatenation and it gives an error trying to concatenate a String with a DATETIME.

DATETIME String Error

Using the CONCAT() function, an implicit conversion will occur and enable this to run without error. Also you can choose to perform an explicit conversion when using the + method for concatenation.

DATETIME String Error

In summary, the CONCAT() function performs an implicit conversion of everything to a string before concatenating it together and returning the result. The + method for concatenation will perform an implicit conversion between string data types only, but if the data types are not of the same type, an explicit conversion has to be performed to achieve a successful result.

Next Steps

Last Update: 7/29/2013

About the author
MSSQLTips author Chad Churchwell
Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips

print tip Print  
Become a paid author

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Monday, November 24, 2014 - 9:50:22 AM - Gerald Read The Tip

I have just found the following Connect topic regarding CONCAT and different collations:



Friday, March 14, 2014 - 9:05:08 AM - Me Read The Tip


SET SignaturePricturePath = '/Files/Data' + CAST(SignaturePricturePath as VARCHAR(MAX)),

ProfilePicturePath = '/Files/Data' + CAST(ProfilePicturePath as VARCHAR(MAX))



Tuesday, December 03, 2013 - 2:51:22 AM - Satish Read The Tip

Nice one.


Thank You

Thursday, August 29, 2013 - 1:32:24 AM - AryanVerma Read The Tip

Nice chad

Saturday, August 24, 2013 - 7:37:40 AM - Izhar Azati Read The Tip

-- Take care to the collate of the fields

create table TestCollate (
 aaa varchar(10) COLLATE Latin1_General_CS_AS_KS_WS,
 bbb char(10) COLLATE Traditional_Spanish_ci_ai

insert into TestCollate( aaa, bbb ) values (' Test 1',' Test 2');

select concat( aaa, bbb ) from TestCollate;

-- ERROR: Cannot resolve the collation conflict between "Latin1_General_CS_AS_KS_WS" and "Traditional_Spanish_CI_AI" in the concat operation.

Monday, July 29, 2013 - 11:46:45 AM - Scott Coleman Read The Tip

On older versions with no CONCAT() function, a lot of other string functions will do implicit conversion.  Instead of "CAST(@integer AS VARCHAR)" you can use "LTRIM(@integer)".  "REPLACE(@varchar, 'xyz', @integer)" also works.

I don't think any of these will convert NULL to an empty string, you'll still need ISNULL() functions.

Monday, July 29, 2013 - 10:39:26 AM - Todd Payne Read The Tip

I think it is important to note that the concat function not only performs the implicit conversion, but it also converts null values to empty strings.





-- Returns NULL


-- Returns 'String1'


More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.