HomeНаука и техникаRelated VideosMore From: TechBrothersIT

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

28 ratings | 15024 views
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 http://sqlage.blogspot.com/2015/03/how-to-concatenate-string-and-integer.html
Html code for embedding videos on your blog
Text Comments (6)
rizma utami (9 months ago)
Thank you. It was very helpful
sm bn (1 year ago)
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
Bobbi Hughes (2 years ago)
This really helped me! Thank you. :)
TechBrothersIT (2 years ago)
+Bobbi Hughes You are most welcome:) , Glad to hear that it was helpful. Thanks for watching!
Elmer Phan (2 years ago)
How would you do the opposite of this?  Lets say I have a field titled Amount and have a data point of 100000 as varchar(50). I need to return results if Amount is >10000. The CAST/CONVERT is not working for me
Harshit Desai (3 years ago)
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

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.