CONVERT nvarchar to numeric

Question

Post reply

CONVERT nvarchar to numeric

jsheldon

SSCertifiable

Points: 5250
More actions
April 16, 2009 at 12:53 pm

#203029

I need to sum up numeric data from an analysis cube however #miss is shown on the fields with zero data.
[Column 13] is the column on table #temp It was from its original table nvarchar.
I am trying to do a convert to 0.00 but am failing with each syntax I am doing.
CONVERT (decimal(2,2),[Column 13])
or
CONVERT (decimal(2,2),[Column 13],2)
Need some assistance..thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Rajib Bahar Ten Centuries Points: 1067 More actions · Answer 1

Your convert function looks fine to me...

I tried this and it worked:

DECLARE @Tmp TABLE ([Column 13] nvarchar(20))

INSERT INTO @Tmp SELECT '0.00'

select CONVERT (decimal(2,2),[Column 13]) from @Tmp

select CONVERT (decimal(2,2),[Column 13],2) from @Tmp

Have you considered using the IsNumeric() function to check whether the data is numerically convertible or not?

Rajib Bahar
http://www.rajib-bahar.com
http://www.twitter.com/rajib2k5
http://www.youtube.com/icsql

jsheldon SSCertifiable Points: 5250 More actions · Answer 2

Hi Rajib,

I did the following query:

SELECT ISNUMERIC(column_13) from #temp

The column brought back 0 and 1s

Again this column that has budget data. So values like 100, 105, 105.67, 9845.43, #MISS appear

I tried to do a SELECT CONVERT(numeric 18,2),column_13) AS convert_col13 INTO #temp2 from #temp but I still get error about unable to convert nvarchar to numeric

fhanlon SSChampion Points: 13131 More actions · Answer 3

to convert to 0.00 format you need CONVERT(decimal(3,2),[column 13]) oytherwise you will get a Arithmetic overflow error converting varchar to data type numeric. error. 0,00 is 3 digits long 2 of which are after the decimal place. decimal(2,2) means your answer is 2 digits long of which 2 digits are to the right of the decimal; example .00

Francis

jsheldon SSCertifiable Points: 5250 More actions · Answer 4

Tried that and no dice

SELECT *, CONVERT(decimal(3,2),[Column 13]) AS CONVERT13

INTO #TEMP2 FROM #TEMP

fhanlon SSChampion Points: 13131 More actions · Answer 5

If you are getting an error please post it

Francis

Rajib Bahar Ten Centuries Points: 1067 More actions · Answer 6

add the error, your temp table script, insert script, and anything else that gives everyone a clear picture

Rajib Bahar
http://www.rajib-bahar.com
http://www.twitter.com/rajib2k5
http://www.youtube.com/icsql

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

jsheldon (4/16/2009)
Hi Rajib,
I did the following query:
SELECT ISNUMERIC(column_13) from #temp
The column brought back 0 and 1s
Again this column that has budget data. So values like 100, 105, 105.67, 9845.43, #MISS appear
I tried to do a SELECT CONVERT(numeric 18,2),column_13) AS convert_col13 INTO #temp2 from #temp but I still get error about unable to convert nvarchar to numeric

The bolded item in your list is at least one of the things that is causing the error. You can't convert '#MISS' to a numeric. You could try doing:

SELECT

CONVERT(numeric(18, 2), column_13) AS convert_col13

INTO

#temp2

from

#temp

WHERE -- only do ones that could be numeric.

ISNUMERIC(column_13) = 1

You could still get some errors as currency characters, '-', and '.' are among characters that cause isnumeric to return 1.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

fhanlon SSChampion Points: 13131 More actions · Answer 8

try using a CASE statement like

SELECT column_13

, CASE ISNUMERIC(column_13)

WHEN 1 THEN CONVERT(numeric(18,2),column_13)

ELSE 0.00

END

FROM #temp

Francis

jsheldon SSCertifiable Points: 5250 More actions · Answer 9

Francis,

Your code was the best fit...thanks to all for the help...

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

jsheldon (4/20/2009)
Francis,
Your code was the best fit...thanks to all for the help...

Just don't rely on ISNUMERIC being some sort of magical ISALLDIGITS function because it's not. Please see the following thread for all the reasons why not. Frank Kalis and I spent a bit of time at it....

http://www.sqlservercentral.com/Forums/Topic243646-8-1.aspx?Highlight=ISALLDIGITS

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fhanlon SSChampion Points: 13131 More actions · Answer 11

Well thats annoying. Although I (only sort of) accept the E notation being accepted. Anyway the referenced discussion was a interesting read. Thanks for pointing that out.

Francis

brian.child Valued Member Points: 65 More actions · Answer 12

Just wanted to say THANK YOU! this issue was making me crazy.....

ichv SSC-Addicted Points: 479 More actions · Answer 13

Hi,

I have exactly the same issue, and the found that solution perfect but it still getting error.

My question is: how could I find the row producing error? I am selecting only ISNUMERIC([column]) = 1 however, get the error:

Msg 8114, Level 16, State 5, Line 1

Error converting data type nvarchar to numeric.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 14

ichv (12/17/2014)
Hi,
I have exactly the same issue, and the found that solution perfect but it still getting error.
My question is: how could I find the row producing error? I am selecting only ISNUMERIC([column]) = 1 however, get the error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

Did you read the article mentioned?

What version of SQL are you using?

Can you post the query that you're using (the relevant parts)?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2