Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Concatenating Int columns

I have a table called Field_Data and the data in the table looks like:

Date                          Track_ID          Item#
2011-02-25 00:00:00.000       70212             1
2011-02-25 00:00:00.000       70212             2
2011-03-09 00:00:00.000       70852             1
2011-03-09 00:00:00.000       70852             3

I am trying to get output as:

Date                          Final_ID
2011-02-25 00:00:00.000       70212_1
2011-02-25 00:00:00.000       70212_2
2011-03-09 00:00:00.000       70852_1
2011-03-09 00:00:00.000       70852_3

I tried doing something like this:

Select Date,Track_ID + '_' + Item# AS Final_ID
From Field_Data

But it gave me following error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '_' to data type int.

Can anyone help me on how to do this?

like image 921
Peter Avatar asked Aug 17 '11 16:08

Peter


People also ask

How do I concatenate two numeric columns in SQL?

Another way to implement Concat in SQL with the numerical value is to use the CAST operator. This operator converts the numerical data into the string format. Using the + (plus) operator will manipulate the numeric data into string concatenation.

How do I concatenate 3 columns in SQL?

To concatenate more than 2 fields with SQL, you can use CONCAT() or CONCAT_WS() function.

How do I concatenate two numerical columns in pandas?

Concatenate Two Columns Using + Operator in pandas By use + operator simply you can concatenate two or multiple text/string columns in pandas DataFrame. Note that when you apply + operator on numeric columns it actually does addition instead of concatenation.

Can you concatenate columns in SQL?

In this article, we will see an SQL query to concatenate two-column into one with the existing column name. We can perform the above activity using the CONCAT() function.


2 Answers

You need to cast the INT fields as varchar:

Select Date,CAST(Trakc_ID as varchar(20)) + '_' + CAST(Item# as varchar(20)) as Final_ID
From Field_Data
like image 191
JNK Avatar answered Sep 30 '22 18:09

JNK


Heads up for any new page visitors, in SQL Server 12+, there's a CONCAT function available.

SELECT CONCAT([Date], [TrackId], '_', [ItemNumber]) AS FinalId
FROM FieldData
like image 28
RJB Avatar answered Sep 30 '22 18:09

RJB