4

I need to do an arithmetic operation to the values saved in SQL table, for example, I have value as 5*10 in next column I want 15 

EQUATION    VALUE
2+5          7
6+8          14

Based on the equation I need to calculate the value.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Girish A
  • 51
  • 1
  • 4
  • So you need to implement a calculator? – EzLo Jul 09 '18 at 14:27
  • What have you tried that is not working? – squillman Jul 09 '18 at 14:27
  • Possible duplicate of [Storing formula (equations) in database to be evaluated later (SQL Server 2005)](https://stackoverflow.com/questions/9722782/storing-formula-equations-in-database-to-be-evaluated-later-sql-server-2005) Or https://stackoverflow.com/questions/46225143/expression-evaluation-in-sql-stored-procedure I wouldn't do this in SQL though. `C#` or some other language would be preferable. [This, for instance, is interesting](https://github.com/zzzprojects/Eval-SQL.NET/wiki/SQL-Server-Eval-%7C-Dynamically-evaluate-arithmetic-operation-and-expression-in-SQL). – JNevill Jul 09 '18 at 14:29
  • Also, generally this is done with an `Eval` function would help for google searches around this topic. Like `Eval SQL Server Arithmetic`. – JNevill Jul 09 '18 at 14:31

2 Answers2

4

As you know by now, SQL Server does not have an EVAL() function. However, with a little dynamic SQL, it is possible, but really not recommended.

Example

Declare @YourTable Table (id int,[EQUATION] varchar(150))
Insert Into @YourTable Values 
 (1,'2+5')
,(2,'6+8')
,(3,'datediff(DAY,''2018-01-01'',getdate())')  -- Added Just for Fun


Declare @SQL varchar(max) = Stuff((Select ',' + concat('(',ID,',',[EQUATION],')')
                                     From @YourTable  A
                                     For XML Path (''))
                                 ,1,1,'')
Exec('Select * from (values ' + @SQL + ')A([ID],[Value])')

Returns

ID  Value
1   7
2   14
3   189
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • solved, but concat function is not available in available in sql server so made minor change Select ',' +'(' + CAST(ID AS VARCHAR(100)) + ','+ [EQUATION] + ')' From TMPTABLE A – Girish A Jul 11 '18 at 11:46
  • @GirishA Happy you were able to make the adjustment ... Just assumed 2012+. – John Cappelletti Jul 11 '18 at 11:47
0

Just execute the operation as a SELECT statement

declare @col1 nvarchar(50) = '10+5'

DECLARE @STATEMENT NVARCHAR(50) = 'SELECT'
DECLARE @COMMAND NVARCHAR(50) = @STATEMENT + ' ' + @COL1

exec sp_executesql @COMMAND

Output:

15

B3S
  • 1,021
  • 7
  • 18