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

Công Nghệ

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


Xem thêm bài viết khác:

5 thoughts on “How to Concatenate String Values with Integer values in SQL Server – TSQL Tutorial

  1. 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

  2. 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

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

    i got a value 'Mary J. Smith'

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

Leave a Reply

Your email address will not be published. Required fields are marked *