How to Concatenate String Values with Integer values in SQL Server - TSQL Tutorial

How to Concatenate String and Integer Values in SQL Server ( + Sign vs CONCAT ) - TSQL Tutorial
Working with Databases is fun. On daily basis we save data into tables in different columns and often we need to write queries which involve concatenation of multiple columns of different datatypes to produce our final output. In below example we have saved some data into #Customer Table which has Street Address column as string and zip as INT. we want to produce Full Address by concatenating these columns. To concatenate we can use + sign but this works only with String values. So if we have any Integer value/s we have to convert them to String first. We can use Cast or Convert function to convert Integer value to string. Also if there is any Null value among which values we are concatenating , our output will be Null by using + sign. To take care of that part we can use either ISNULL() or Coalesce Function to replace NULL with blank value in SQL Server.

In SQL Server 2012 and Later versions, CONCAT function was introduced. You can concatenate multiple values by using this function. Fun part of this function is you don't have to worry about Null outcome as it will Ignore Null values and also you don't have to worry about converting INT into strings, It will take care of that automatically. So if you are using new versions such as 2012 or later, start taking advantage of this function.

Blog post link for the video with script

[harshitdesai]

hi, if we want to separate values which is in single column


suppose
i got a value 'Mary J. Smith'

in this case if i want to separate 'Mary', 'J', 'Smith' into different columns then... plz help

[smbn]

hi,
I have null values in table t2 but
select isnull(adress, ' ') from t2
not working its giving output with null only
same when i am using :
coalesce(adress, ' ') from t2

[wiseman]

How can I concatenate the value of a primary key? That is string and int identity

[mgteracer]

How would you do the opposite of this?  Lets say I have a field titled Amount and have a data point of as varchar(50). I need to return results if Amount is >10000. The CAST/CONVERT is not working for me