Need help converting nvarchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Need help converting nvarchar to numeric

Author

Topic

prototype18
Starting Member

7 Posts

Posted - 2010-11-22 : 14:47:00

I have some data in my table that needs to be converted from nvarchar to numeric, but fails when trying to map the correct data type from the ASC file.

So I was able to write this query to convert the data and make a new column...

SELECT [Tax]
CASE ISNUMERIC([Tax])
WHEN 1 THEN CONVERT(numeric(13,2),[Tax]
ELSE 0.00
END

I saved the output into a file and would like to drop that column and reload it with the new data..

Does anyone know the best way to go about doing this?

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-22 : 16:31:18

my guess is you have non numeric data in your nvarchar column

What do you see if you run

SELECT '"'+[Tax]+'"'
FROM Table
WHERE ISNUMERIC([Tax]) = 0

Rob posted about this last week

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

prototype18
Starting Member

7 Posts

Posted - 2010-11-23 : 09:03:41

Well there are blanks where there should be 0's.

Is there a way to bring this data in as a numeric(13,2) by maybe bringing them in as nvarchar and then adding the 0's then convert the data?

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-23 : 09:17:13

SELECT CASE CONVERT(decimal(13,2),WHEN [Tax] = ' ' Then 0 ELSE [Tax] END)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

prototype18
Starting Member

7 Posts

Posted - 2010-11-23 : 09:39:29

says incorrect syntax near WHEN

prototype18
Starting Member

7 Posts

Posted - 2010-11-23 : 09:49:11

I tried using your query and it would not let me.

I tried modifiying it as such, but still with no success...

SELECT [Tax]
CASE CONVERT(decimal(13,2),[Tax])
WHEN [Tax] = ' ' THEN 0
FROM dbo.TXBK

I'm not sure what is wrong...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-11-23 : 16:28:41

The ISNUMERIC function does not always accurately determine when a string is a numeric value. If I recall corrctly, there are eight single character non-numeric strings that are reported as passing ISNUMERIC but throw an error when you try to CONVERT them. It's unfortunate but you'll need additional logic to handle the discrepancy. Can you determine which strings are passing ISNUMERIC but are failing the CONVERT?

=======================================
No matter what side of the argument you are on, you always find people on your side that you wish were on the other. -Jascha Heifetz, violinist (1901-1987)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 09:56:27

quote:
Originally posted by X002548

my guess is you have non numeric data in your nvarchar column

What do you see if you run

SELECT '"'+[Tax]+'"'
FROM Table
WHERE ISNUMERIC([Tax]) = 0

Rob posted about this last week

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Did you know isnumeric() is a problamatic function?

select
isnumeric(','),
isnumeric('.'),
isnumeric('12d3'),
isnumeric('$')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources