CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
Peter
Peter

Reputation: 651

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?

Upvotes: 9

Views: 24738

Answers (2)

RJB
RJB

Reputation: 2103

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

Upvotes: 5

JNK
JNK

Reputation: 65157

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

Upvotes: 21

Related Questions