CONCAT() The forgotten T-SQL function

We’ve all been in a situation before where you’re attempting to concatenate a string and integer values together and been presented with the below error.

Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the varchar value [VALUE] to data type int.

which is usually greeted by a loud sigh.

Let me explain further, lets say for example your trying to concatenate two fields together from a table, one is a textual data type (char, varchar, nvarchar) and the second is a numerical data type (int, decimal, numeric), something along the lines of the example below.

SELECT 'SQL is Number '+1

You’ll then be met with the conversion error like above.

Well, there is a better way, CONCAT(), using this function for the same example as above will not result in a conversion error.

SELECT CONCAT('SQL is Number ',1)

This function removes all need to convert values just to concatenate text and string values together, but for some reason whenever I see code posted on various websites, even from SQL Server MVP’s they seem never to utilise this function.

One excellent use for this is when you attempting to created the SQL Statement for a dynamic SQL execution.  Instead of the usual conversions and + signs joining the text and parameters together its far simpler.

The below example shows the difference between the two statements, it might be a little change but to me its far simpler and far easier to read.

 

</pre>
DECLARE @Num INT
DECLARE @Num2 INT

SELECT @Num = 1, @Num2 = 2

/*Old version*/
SELECT 'SELECT * FROM Test WHERE Number BETWEEN '+CONVERT(CHAR(1),@Num)+' AND '+CONVERT(CHAR(1),@Num2)

/*Old version*/
SELECT CONCAT('SELECT * FROM Test WHERE Number BETWEEN ',@Num,' AND ',@Num2)

 

Hope this helps someone out there simplify their code.

 

 

 

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s