nvarchar to numeric conversion

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Dear All,

I have following query:

ChargeFormula varchar(8000) null
Charge numeric(18, 0) null

I have following value in the table ChargeMaster

ChargeID : 1003
ChargeDesc : SinkingFund
ChargeFormula : (400.00*0.75)/100)/12
Charge : NULL
ChargeStatus : Y

Now I want to use the formula which is in the ChargeFormula field and put the value calculated in the Charge column. But the ChargeFormula column is nvarchar and Charge is numeric. I am facing problem to convert the value.

Please Help.

Thanks in Advance..

Vinay D Sarmalkar

Posted 15-Mar-11 4:05am

vinay.sarmalkar

Updated 15-Mar-11 4:24am

Henry Minute

v2

Add a Solution

3 solutions

Solution 1

Try this

declare @tmp varchar(100)
select @tmp=formula from table
exec sqltextquery @tmp

Posted 15-Mar-11 4:33am

Husain Ahmad Khalid

Solution 2

Something like below will convert to int but as you have Charge numeric(18, 0) null output returns 0(as you have 0 decimal places to return) where actual output is 0.25000

SQL

select cast((select (((400.00*0.75)/100)/12) as test) as int)

Posted 15-Mar-11 4:37am

m@dhu

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)

Henry Minute · Accepted Answer · 2011-03-15T04:39:00

Solution 3

This[^] thread has a Stored Procedure definition that does what the two previous replies do. Which you may find useful.

Posted 15-Mar-11 4:39am

Henry Minute

Comments

Espen Harlinn 15-Mar-11 17:49pm

Right, my 5 - you obviously meant sp_executesql :)