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 2012 Forums
 SSIS and Import/Export (2012)
 Converting nvarchar to decimal?

Author  Topic 

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-12-05 : 09:57:35
How can I covert a column that is nvarchar with a value of 97.40% to a decimal(5,4)?

I tried the following but is not working for me.

-- I'm doing the substring to cut off the '%' sign --
cast(Convert(numeric,Substring(Holder6,1,5))as decimal(5,4)) as Positive_Rate

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-12-05 : 11:52:39
quote:
Originally posted by mgreen84

How can I covert a column that is nvarchar with a value of 97.40% to a decimal(5,4)?

I tried the following but is not working for me.

-- I'm doing the substring to cut off the '%' sign --
cast(Convert(numeric,Substring(Holder6,1,5))as decimal(5,4)) as Positive_Rate

You can't store 97.4 in decimal (5,4).

Regardless, if you want to remove the percentage symbol, it might be easier to use replace as in:
REPLACE(Holder6,'%','')
Go to Top of Page

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-12-05 : 13:30:18
quote:
Originally posted by James K

quote:
Originally posted by mgreen84

How can I covert a column that is nvarchar with a value of 97.40% to a decimal(5,4)?

I tried the following but is not working for me.

-- I'm doing the substring to cut off the '%' sign --
cast(Convert(numeric,Substring(Holder6,1,5))as decimal(5,4)) as Positive_Rate

You can't store 97.4 in decimal (5,4).

Regardless, if you want to remove the percentage symbol, it might be easier to use replace as in:
REPLACE(Holder6,'%','')





How would I store 97.4? numeric?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-05 : 13:48:08
decimal(3,1)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-12-09 : 09:07:44
quote:
Originally posted by tkizer

decimal(3,1)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Thanks this worked!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-09 : 12:37:27


In case 100.0 is an option, use 4,1 to account for 3 numbers before the decimal.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -