Multiplication of two columns

Question

0.00/5 (No votes)

See more:

I have read an excel sheet and inserted it into an table in sql. now i want to perform multiplication of two columns and store the result in 3rd column. the problem i m facing is since the table is read from an excel sheet those columns are in nvarchar data type. now i m not able to convert those data type in int or float. while i run the query select (a) * (b) as Tot from table1. i m getting the error as Operand data type nvarchar is invalid for multiply operator.. how can i avoid this?. i m not able to change the date type in sql table. In excel sheet also i've formatted those columns as number. but still while reading into sql server the data type is still varchar.

Posted 11-Aug-13 23:09pm

ARUN K P

Updated 11-Aug-13 23:15pm

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-08-11T23:17:00

Have a look at examples.

Below query:

SQL

SELECT A*B AS C
FROM (
	SELECT '123' AS A, '2' AS B
	) AS T

raises error:

Msg 8117, Level 16, State 1, Line 2
Operand data type varchar is invalid for multiply operator.

Why? Because of varchar data type. In this case numbers are stored as a text!!!

This query:

SQL

SELECT CONVERT(INT,A)*CONVERT(INT,B) AS C
FROM (
	SELECT '123' AS A, '2' AS B
	) AS T

returns: 246

Conclusion: Use appropriate data types[^]!

OriginalGriff · Accepted Answer · 2013-08-11T23:18:00

Solution 2

Try:

SQL

SELECT CONVERT(INT, a) * CONVERT(INT, b) AS Tot FROM Table1

[edit]Forgot the "1" of "Table1" - OriginalGriff[/edit]

Posted 11-Aug-13 23:18pm

OriginalGriff

Updated 11-Aug-13 23:19pm

v2

Comments

ARUN K P 12-Aug-13 5:21am

Thanks griff. tat above query works fine.

OriginalGriff 12-Aug-13 5:25am

You're welcome!

Maciej Los 12-Aug-13 5:23am

+5

CPallini · Accepted Answer · 2013-08-11T23:20:00

Solution 3

You may use the SQL CAST operator, see the documentation[^] for examples.

Posted 11-Aug-13 23:20pm

CPallini

Comments

Maciej Los 12-Aug-13 5:23am

+5

CPallini 12-Aug-13 5:25am

Thanks.

ARUN K P 12-Aug-13 5:49am

+5

CPallini 12-Aug-13 6:29am

Thank you.

Aarti Meswania · Accepted Answer · 2013-08-11T23:24:00

Solution 4

SQL

select cast(a as float) * cast(b as float) as Tot from tablename

Happy Coding!
:)

Posted 11-Aug-13 23:24pm

Aarti Meswania

Comments

ARUN K P 12-Aug-13 5:57am

+5

Aarti Meswania 12-Aug-13 6:07am

Thank you! :)

jaideepsinh 26-Aug-13 9:37am

5+ve.

Aarti Meswania 26-Aug-13 12:24pm

thank you! :)

jaideepsinh 27-Aug-13 0:37am

YW..By the way where you from.

Multiplication of two columns

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us