Convert varchar to numeric ms SQL

Question

1.11/5 (2 votes)

See more:

NEED TO

CONVERT  VARCHAR TO NUMERIC MS SQL

I HAVE ERROR IN MY CODE

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

THANK YOU

What I have tried:

SELECT
    c.sifra AS 'Šifra RM',
    c.naziv AS 'Naziv RM',
    SUM(c.kolicina) AS 'Kol RM',
    '' AS 'Prodano danas',
    '' AS 'Kol prod danas'
FROM
    popis_repromaterijal_roba AS c
GROUP BY
    c.sifra,
    c.naziv

UNION

SELECT
    '',
    '',
    '',
    d.roba,
    SUM(d.kolicina)
FROM
    mp_racun_roba AS d
GROUP BY
    d.roba

Posted 27-Jul-23 21:13pm

Stylus STYLUS

Updated 27-Jul-23 21:31pm

v2

Add a Solution

Comments

Richard MacCutchan 28-Jul-23 3:28am

Which item do you need to convert, and why?

Stylus STYLUS 28-Jul-23 3:29am

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Richard MacCutchan 28-Jul-23 3:33am

Which item and what exactly are you trying to do? We have never seen the code you have posted, we have no idea how your database columns are defined, or what you are trying to do with the data. So stop being cryptic, and provide proper detailed explanations if you want us to help.

Dave Kreskowiak 17-Aug-23 9:25am

Fix the structure of your table to store the numeric value in a more appropriate type. Doing large queries where you have to convert a string to a numeric value will greatly slow down your queries as every record returned needs to have a conversion done on it before being returned.

2 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)

OriginalGriff · Answer 1 · 2023-07-27T21:31:00

"It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages.
Help us to help you!

Since you don't tell us where the error occurs (it's on the error message) or what the error is (it's also on the error message) and we have no access to your DB, we have to guess.

And most likely, it's this:

SQL

SUM(c.kolicina) AS 'Kol RM',

And treh problem is that the kolicina column isn't numeric, and non-numeric columns can't be handled by match functions like SUM.

And that leads to a bigger problem: your DB design is wrong and needs to be changed.
Never store nuymbers in NVARCHAR columns - always use INT, FLOAT, or DECIMAL instead.
If you use the wrong column datatype it's easy to set up, and lazy to get the user data into - but it always gives total nightmares after that - because the data in your DB is not valid, or is not in a consistent format, or is in a different format from that which SQL expects.

Think about it: 10/11/12 is a valid date. But is it 10th Nov 2012, 11th Oct 2012, 12th Nov 2010, or some other value entirely? The only time you can tell is when the user inputs the value, and you use his culture to convert it to a DateTime value - as soon as it arrives in the DB it's too late because you no longer have any idea what date format he used: it could be US: MM/DD/YY, European: DD/MM/YY, or ISO / Japanese YY/MM/DD - and you don't even know that the user is using the same calendar as you so the year could be well different (the Hijri date today is Jumada Al-Awwal 3, 1438)! Or even that he didn't enter "hello, my name is Jackie" which isn't even close to a date.

So when you try to convert it to a date at a later time you are almost guaranteed to get errors because the SQL server will try to convert it using it's culture - and generally you don't even know what culture the server is set to!

And you get the same problems when you store numbers as strings as well: they have to be converted every time you want to use them, they can be in the "wrong format" because the user that entered them uses Indian numbering instead of US: "2,31,50,000" instead of "23,150,000", or German: "23 150 000". Or the field may contain "Hello world" through a coding error or a lack of validation.

Always use appropriate data types - it may be easier for your code to slam in NVARCHAR all the time, but it wastes huge amounts of effort later fixing up the holes it leaves.

5th LMNt · Answer 2 · 2023-08-16T09:19:00

If c.kolicina are all numbers but stored as strings (field type nvarchar or something), then yes, you should absolutely fix that. If you don't have the level of access to your database to do so, then ask your IT department to fix it.

In the meantime, you could try:
sum(cast(c.kolina as int)) as 'Kol RM'

That assumes the numbers are integers. You may need a different data type.

Convert varchar to numeric ms SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0