Evaluating Varchar String with Math

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)

Evaluating Varchar String with Math

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2008-03-05 : 16:00:58

In reference to this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50392

madhivanan asks for sample table data to avoid using a cursor. I could really use this, so here is the table

[CODE]

CREATE TABLE #T (
FORMULA VARCHAR(15),
ANSWER decimal(15,8)
)
INSERT INTO #T SELECT '1', NULL
INSERT INTO #T SELECT '1.0 + 2', NULL
INSERT INTO #T SELECT '1/2', NULL
INSERT INTO #T SELECT '1/2+2', NULL
INSERT INTO #T SELECT '1/(2+2)',NULL
INSERT INTO #T SELECT '3*7.5', NULL
INSERT INTO #T SELECT '3/2*.6-2', NULL
INSERT INTO #T SELECT '(1-1)', NULL

SELECT * FROM #T

-- RUN THE MATH FUNCTIONS, UPDATE THE TABLE'S ANSWER COLUMN WITH THE ANSWER

SELECT * FROM #T

DROP TABLE #T
[/CODE]

I would expect this output:


FORMULA     ANSWER
====================
1	    NULL
1.0 + 2	    NULL
1/2	    NULL
1/2+2	    NULL
1/(2+2)	    NULL
3*7.5	    NULL
3/2*.6-2    NULL

FORMULA     ANSWER
====================
1	    1.00000000
1.0 + 2	    3.00000000
1/2	    0.50000000
1/2+2	    2.50000000
1/(2+2)	    0.25000000
3*7.5	    22.50000000
3/2*.6-2    -1.10000000
(1-1)	    0.00000000

Any help appreciated, thanks,

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 19:40:47

[code]

CREATE TABLE #T (ID int not null,
FORMULA VARCHAR(15),
ANSWER decimal(15,8)
)
INSERT INTO #T SELECT 1,'1', NULL
INSERT INTO #T SELECT 2,'1.0 + 2', NULL
INSERT INTO #T SELECT 3,'1/2', NULL
INSERT INTO #T SELECT 4,'1/2+2', NULL
INSERT INTO #T SELECT 5,'1/(2+2)',NULL
INSERT INTO #T SELECT 6,'3*7.5', NULL
INSERT INTO #T SELECT 7,'3/2*.6-2', NULL
INSERT INTO #T SELECT 8,'(1-1)', NULL
--

Update #T
SEt FORMULA = replace(FORMULA,'/','.0/')

-- RUN THE MATH FUNCTIONS, UPDATE THE TABLE'S ANSWER COLUMN WITH THE ANSWER
Declare @formula varchar(100),@ID int
Declare @result decimal(15,8)
Declare @SQL varchar (8000)
DECLARE myFormula CURSOR
FOR

Select ID,FORMULA
from #t

OPen myFormula

FETCH NEXT FROM myFormula into @ID,@formula
While (@@FETCH_STATUS <> -1)
Begin

SET @SQL = 'Update #T SET ANSWER = cast(' + @formula + ' as decimal(15,8)) where ' + cast(@ID as varchar) + ' = ID'
Print @SQL
Exec (@SQL)
FETCH NEXT FROM myFormula into @ID,@formula
END

CLOSE myFormula
Select * from #T
DEALLOCATE myFormula

Drop Table #T

--
--
--I would expect this output:
--
--
--FORMULA ANSWER
--====================
--1 NULL
--1.0 + 2 NULL
--1/2 NULL
--1/2+2 NULL
--1/(2+2) NULL
--3*7.5 NULL
--3/2*.6-2 NULL
--

[/code]

results:
[code]
--FORMULA ANSWER
--====================
1 1 1.00000000
2 1.0 + 2 3.00000000
3 1.0/2 0.50000000
4 1.0/2+2 2.50000000
5 1.0/(2+2) 0.25000000
6 3*7.5 22.50000000
7 3.0/2*.6-2 -1.10000000
8 (1-1) 0.00000000
[/code]

Couldn't get around the cursor part...maybe someone else will have better luck

Poor planning on your part does not constitute an emergency on my part.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-05 : 22:41:51

No cursor... just an SQL "inner loop"

CREATE TABLE #T (	
	FORMULA VARCHAR(15),
	ANSWER decimal(15,8)
)
INSERT INTO #T SELECT '1', NULL
INSERT INTO #T SELECT '1.0 + 2', NULL
INSERT INTO #T SELECT '1/2', NULL
INSERT INTO #T SELECT '1/2+2', NULL
INSERT INTO #T SELECT '1/(2+2)',NULL
INSERT INTO #T SELECT '3*7.5', NULL
INSERT INTO #T SELECT '3/2*.6-2', NULL
INSERT INTO #T SELECT '(1-1)', NULL

SELECT * FROM #T

DECLARE @SQL VARCHAR(MAX)

 SELECT @SQL = ISNULL(@SQL+CHAR(13),'') 
      + 'UPDATE #T SET ANSWER = ' + REPLACE(Formula,'/','.0/') + ' WHERE Formula = ' + QUOTENAME(Formula,'''')
   FROM #t

PRINT @SQL

EXEC (@SQL  )

SELECT * FROM #T

DROP TABLE #T

--Jeff Moden

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 22:50:53

cool...thanks Jeff...I hadn't come back to this to work on it more.

Poor planning on your part does not constitute an emergency on my part.

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2008-03-06 : 08:34:07

That's amazing, thanks for the lesson!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-06 : 09:05:48

Thank you both for the feedback.

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 09:12:29

And if you want to simulate the same in SQL Server 2000, then aware of maximum of 8000 charaters for varchar

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-06 : 09:24:51

Heh... the real key is that this type of stuff should probably be done in a spreadsheet... not SQL.

--Jeff Moden

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 09:47:48

maybe he imported the formulas from Excel as text and cut off the '=' ;)

Poor planning on your part does not constitute an emergency on my part.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-06 : 10:10:19