CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
giles
giles

Reputation: 843

very simple column addition

I'm under the impression that SQL Server will allow me to perform addition such as

SELECT     val01, val02, val03 = (val01 + val02)
FROM       Table_1

so that val03 is the addition of the two previous columns. However, in this instance I am literally seeing value 1 next to value 2. Can you show me where I'm going wrong?

Upvotes: 0

Views: 127

Answers (3)

Jennifer S
Jennifer S

Reputation: 1449

If your val01 and val02 columns are character or varchar, but contain numbers, the numbers are going to be concatenated in the syntax above.

If you want them to appear as numbers, you will need to use CAST or CONVERT on the val01 or val02 fields to get them to be added as numeric values.

Example:

SELECT val01, val02, val03 FROM Table_1 WHERE CAST(val03 as INT) = ((CAST val01 as INT) + (CAST val02 as INT))

Upvotes: 0

JNK
JNK

Reputation: 65157

The + operator has two uses in SQL Server:

  • For numbers (int, bigint, decimal, etc) it is an ADDITION operator.
  • For strings (varchar, nvarchar, char, etc) it is a CONCATENATION operator.

If you have numerical data in a string datatype field, you will see the second value appended to the first.

The solution is to either:

  • Use correct datatypes. If it's a number, the datatype should be numerical.
  • CAST to the correct datatype - i.e. val03 = (CAST(val01 as int) + CAST(val02 as INT))

Upvotes: 3

jwrush
jwrush

Reputation: 753

So val03 is a concatenation of val01 and val02 in your result list? Like:

VAL01  VAL02  VAL03
    1      2   1  2
    4      5   4  5

In this case, either Val01 or Val02 are non-numerical columns, or maybe both. Cast them:

SELECT val01, val02, val03 = (cast(val01 as float) + cast(val02 as float)) FROM Table_1

Or, alternatively, fix the column types.

Upvotes: 1

Related Questions