SQL Convert statement

Question

I need help with a SQL convert statement. I have NetQuanity (masterTable) which is a varchar(15) and I have another table with Purchase price (PO TABLE) which is money. When I try to multiply them in a SQL view is gives me the error:

enter image description here

NetQuanity (masterTable) which is a varchar(15) - why? Is there a specific need for storing generaly numeric value as a string? — Sergey Kudriavtsev, Dec 02 '11 at 20:40
What kind of data do you have in your varchar(15) column? `'xyz'`? Is the data convertible to a numeric type? — Albin Sunnanbo, Dec 02 '11 at 20:41
The real fix is to fix your structure. Numeric data that you want touse for math calulations shouel never be storedina varchar field. YOu don't want to do expensive conversions every time you need to use the data as it was intended. There are times to refacor datbases and this is one of them. — HLGEM, Dec 02 '11 at 21:15

score 4 · Accepted Answer · edited May 23 '17 at 12:19

If your field is a VARCHAR, you'll need to CAST to the appropriate data type prior to your operation. e.g.

CAST(myVarCharField as INT) * myIntField

Be forewarned however, if you attempt to CAST this field to a numeric data type and it's not numeric, you'll be in the same boat.

I would recommend using CAST over CONVERT in your example, for the following reason defined in this SO post:

Related: T-SQL Cast versus Convert

score 2 · Answer 2 · answered Dec 02 '11 at 20:41

2

Maybe try using the CONVERT function? CONVERT(money,NetQuantity).

answered Dec 02 '11 at 20:41

Michael Krelin - hacker

138,757
24
193
173

Also, make sure your `varcharcolumn` does not have invalid characters that will break the conversion. In which case you will get something like `Cannot convert a char value to money. The char value has incorrect syntax.` – omarello Dec 02 '11 at 20:44

score 0 · Answer 3 · answered Dec 02 '11 at 20:51

First of all you have a data definition problem. The first thing is to eliminate any non-numeric entries in the master table.

SELECT whatever FROM masterTable WHERE ISNUMERIC(NetQuanity)=1

The next step is to include this as a sub-query in the calculation.
In this query use CONVERT or CAST to convert the valid quanities to integer. i.e.

CONVERT(INT, NetQuantity)

SQL Convert statement

3 Answers3