Menu
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?
921
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.
To concatenate more than 2 fields with SQL, you can use CONCAT() or CONCAT_WS() function.
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.
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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
