VARCHAR to Decimal Conversion - SQL Server Forums

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

Transact-SQL (2005)

VARCHAR to Decimal Conversion

Author

Topic

dav1djed
Starting Member

10 Posts

Posted - 2007-03-31 : 02:39:35

I have a table with field named AMOUNT which is a decimal(30,5) and a field named FORUMLA which is a varchar(max) field. I build the formulas dynamically and they all vary with length and context. I currently am selecting the FORMULA field and saving the actual value and then saving into the AMOUNT field one row at a time. The problem is that I have huge datasets and this will take weeks to complete. I need some type of statement similar to

UPDATE dbo.TABLE
SET AMOUNT = CONVERT( decimal(30,5), FORMULA)

But of course I can’t convert a VARCHAR to a numeric field. Does anyone have a suggestion?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-31 : 02:45:44

How does your FORMULA looks like ? Maybe you use Dynamic SQL to calculate the result and store in AMOUNT

dav1djed
Starting Member

10 Posts

Posted - 2007-03-31 : 02:55:09

The fomulas all vary greatly. Could be "10 / 6" for one or another could be "POWER((8.0007/134) , (.25)) - 1 ". Some are very simple others are extremely complex. All the formulas can be modified dynamically by the end users in the front end tool I created.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-31 : 03:09:55

These won't be easy. Why do you want to do this in the first place ?

dav1djed
Starting Member

10 Posts

Posted - 2007-03-31 : 03:13:53

quote:
Originally posted by khtan

These won't be easy. Why do you want to do this in the first place ?

KH

It is needed for performance issues. Building it one at a time was taking 1.5 hours per variable type. I can create the formulas through update in less then 3 minutes, so if I can create a final one to convert to amount I can go from 1.5 hours to 5 or 6 minutes. Make a huge impact with roughly 130 current seperate variables.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-31 : 03:45:42

try this :


drop table #t
-- Create temp table for testing
create table #t
(
	ID	int identity(1,1),
	FORMULA	varchar(100),
	AMOUNT	decimal(30, 5)
)

-- create some sample data
insert into #t(FORMULA)
select	'POWER((8.0007/134) , (.25)) - 1'	union all
select	'10/6'


declare @sql 	varchar(100),
	@id	int

select	@id = 0
select 	@id = min(ID) from #t where ID > @id

while 	@id is not null
begin	
	select	@sql = 'UPDATE t SET AMOUNT = ' + FORMULA + char(13) FROM #t where ID = @id
	select	@sql = @sql + 'FROM #t t WHERE ID = ' + convert(varchar(10), @id)
	print	@sql
	exec	(@sql)
	select 	@id = min(ID) from #t where ID > @id
end

select	*
from	#t

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-31 : 03:50:23

Note : It is assuming that the 'FORMULA' is in T-SQL syntax and functions

dav1djed
Starting Member

10 Posts

Posted - 2007-03-31 : 03:56:41

quote:
Originally posted by khtan

Note : It is assuming that the 'FORMULA' is in T-SQL syntax and functions

KH

Thank you I will try this

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-31 : 10:43:18

this will be tough...you need to supply some sample data, ddl, and expected results for us to help you

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

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources