Hi Everyone,
In this post I will be exploring the the concatanation operator to concatenate data from multiple columns and display the result as a single column
Consider a sample table with the name Tbl_Employee_Master
Emp_ID Emp_Age Emp_Name
001 023 alpha
002 032 bravo
003 029 charlie
In case our requirement(usually very rarely) is to display as follows
001023alpha
002032bravo
003029charlie
Then if we write the query as follows
select Emp_ID+Emp_Age+Emp_Name from Tbl_Employee_Master
The execution of the query will give an error saying
"Conversion failed when converting the varchar value to data type int."
The reason for the error is that the compiler tries to convert the values in
the Emp_Name column of datatype varchar to datatype int
The below query
select Emp_ID+Emp_Age from Tbl_Employee_Master
will give the sum of EmpId and Emp_Age as both are int datatype
and + operator functions as a addition operator instead of concatenation operator
Hence for the + operator to work only as a concatenation operator
we supply only the varchar parameter by converting the int values to varchar values as below
SELECT convert(varchar,Emp_ID) +convert(varchar,Emp_Age)+Emp_Name FROM Tbl_Employee_Master
The above query will work properly because we are converting the value from int data type to varchar and then we are concatenating the varchar values with each other.
The above query will display the result as follows
(No column name)
001023alpha
002032bravo
003029charlie
2)If we want to give a name to our column then we can write the query as
SELECT (convert(varchar,Emp_ID) +convert(varchar,Emp_Age)+Emp_Name) AS Employee_Details FROM Tbl_Employee_Master
The above query will display the result as follows
Employee_Details
001023alpha
002032bravo
003029charlie
3)If we want to add a character like - (dash) or any other character between the columns data then we can add it as follows
SELECT convert(varchar,Emp_ID) +'-' + convert(varchar,Emp_Age) +'-' + Emp_Name FROM Tbl_Employee_Master
The above query will display the result as follows
Employee_Details
001-023-alpha
002-032-bravo
003-029-charlie
4) If we want to append some static text to the begining of the result then we can write the query as
SELECT 'The employee details are ' +convert(varchar,Emp_ID) +'-' + convert(varchar,Emp_Age) +'-' + Emp_Name FROM Tbl_Employee_Master
The above query will display the result as follows
(No column name)
The employee details are 001-023-alpha
The employee details are 002-032-bravo
The employee details are 003-029-charlie
Further Reading
http://blog.sqlauthority.com/2010/11/25/sql-server-concat-function-in-sql-server-sql-concatenation/
In this post I will be exploring the the concatanation operator to concatenate data from multiple columns and display the result as a single column
Consider a sample table with the name Tbl_Employee_Master
Emp_ID Emp_Age Emp_Name
001 023 alpha
002 032 bravo
003 029 charlie
In case our requirement(usually very rarely) is to display as follows
001023alpha
002032bravo
003029charlie
Then if we write the query as follows
select Emp_ID+Emp_Age+Emp_Name from Tbl_Employee_Master
The execution of the query will give an error saying
"Conversion failed when converting the varchar value to data type int."
The reason for the error is that the compiler tries to convert the values in
the Emp_Name column of datatype varchar to datatype int
The below query
select Emp_ID+Emp_Age from Tbl_Employee_Master
will give the sum of EmpId and Emp_Age as both are int datatype
and + operator functions as a addition operator instead of concatenation operator
Hence for the + operator to work only as a concatenation operator
we supply only the varchar parameter by converting the int values to varchar values as below
SELECT convert(varchar,Emp_ID) +convert(varchar,Emp_Age)+Emp_Name FROM Tbl_Employee_Master
The above query will work properly because we are converting the value from int data type to varchar and then we are concatenating the varchar values with each other.
The above query will display the result as follows
(No column name)
001023alpha
002032bravo
003029charlie
2)If we want to give a name to our column then we can write the query as
SELECT (convert(varchar,Emp_ID) +convert(varchar,Emp_Age)+Emp_Name) AS Employee_Details FROM Tbl_Employee_Master
The above query will display the result as follows
Employee_Details
001023alpha
002032bravo
003029charlie
3)If we want to add a character like - (dash) or any other character between the columns data then we can add it as follows
SELECT convert(varchar,Emp_ID) +'-' + convert(varchar,Emp_Age) +'-' + Emp_Name FROM Tbl_Employee_Master
The above query will display the result as follows
Employee_Details
001-023-alpha
002-032-bravo
003-029-charlie
4) If we want to append some static text to the begining of the result then we can write the query as
SELECT 'The employee details are ' +convert(varchar,Emp_ID) +'-' + convert(varchar,Emp_Age) +'-' + Emp_Name FROM Tbl_Employee_Master
The above query will display the result as follows
(No column name)
The employee details are 001-023-alpha
The employee details are 002-032-bravo
The employee details are 003-029-charlie
Further Reading
http://blog.sqlauthority.com/2010/11/25/sql-server-concat-function-in-sql-server-sql-concatenation/
