5

i want to evaluate a mathematical expression saved in a variable in sql server

i google this a lot and found 3 solution but not applicable in my scenario

1- this solution cannot be executed inside a function but i need it inside a function

declare @expression nvarchar(max)
set @expression = '2*3*100'

declare @sql nvarchar(max)
set @sql = 'select @result = ' + @expression

declare @result int
exec sp_executesql @sql, N'@result int output', @result = @result out

select @result

2- this cannot be saved into a variable but i need to store the result into a variable

DECLARE @LocalVariable VARCHAR(32);
SET @LocalVariable = '2*3*100';
EXEC('SELECT ' + @LocalVariable);

3- the last solution i found gives me a error

DECLARE @x xml 
DECLARE @v decimal(20,4) 
SET @x = '' 
DECLARE @calculatedDataString nvarchar(1000) = '(1 div 100)*((118 div 100)*300.000000)' 
SET @v= @x.value('sql:variable("@calculatedDataString")', 'decimal(20,4)') 
SELECT @v

the error is

Msg 8114, Level 16, State 5, Line 5
Error converting data type nvarchar to numeric.

please advice

Mariam
  • 533
  • 2
  • 12
  • 22
  • The simple answer is you can't. You must use dynamic sql for this kind of thing and you can't execute dynamic sql in a function. – Sean Lange Mar 29 '17 at 18:40
  • what can i do :( – Mariam Mar 29 '17 at 18:55
  • 1
    Why does it have to be a function? Can you use a stored procedure instead? – Sean Lange Mar 29 '17 at 19:02
  • because it is used by many other functions to send parameters and receive a return value – Mariam Mar 29 '17 at 19:05
  • could you please help me on this ... this is working in a function but give an error on converting ... DECLARE @x xml DECLARE @v decimal(20,4) SET @x = '' DECLARE @calculatedDataString nvarchar(1000) = '(1 div 100)*((118 div 100)*300.000000)' SET @v= @x.value('sql:variable("@calculatedDataString")', 'decimal(20,4)') SELECT @v – Mariam Mar 29 '17 at 19:06
  • Then you are stuck. You either need to store the calculated value or forget using a function. – Sean Lange Mar 29 '17 at 19:07
  • Can you explain how that xml code is working but it gives an error? – Sean Lange Mar 29 '17 at 19:09
  • this code gives an error ...... DECLARE @x xml DECLARE @v decimal(20,4) SET @x = '' DECLARE @calculatedDataString nvarchar(1000) = '(1 div 100)*((118 div 100)*300.000000)' SET @v= @x.value('sql:variable("@calculatedDataString")', 'decimal(20,4)') SELECT @v – Mariam Mar 29 '17 at 19:16
  • Yes that code produces an error. But you also said it is working. Does it work or not? – Sean Lange Mar 29 '17 at 19:23
  • it is applicable inside a function but gives an error on execution .. i mean it compiles correct but give an error in runtime .. this means if the error solved it will execute inside a function ... i think – Mariam Mar 29 '17 at 19:26
  • could you help me to solve this error :) please – Mariam Mar 29 '17 at 19:30
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/139414/discussion-between-sean-lange-and-mariam). – Sean Lange Mar 29 '17 at 19:32
  • what is this chat – Mariam Mar 29 '17 at 19:37
  • Probably there is a simpler solution, can you give us a broader view, why you need the feature. Probably @Cappelletti's solution can be adapted – Serg Mar 30 '17 at 07:25

4 Answers4

3

Perhaps this may help.

The following will evaluate a series of expressions, save the results to a #temp table. From there you can store the individual results into a variable

This is a dramatically scaled down version. The full one was built for macro substituions (i.e. calculate a series or Finanancial Ratios for multiple datasets)

If you provide a more robust USE CASE, perhaps I can help further

Example

Declare @Expression table (ID int,Expression varchar(max))
Insert Into @Expression values
 (1,'(1/100.0)*((118/100.0)*300.00000)')           -- Simple Calculation
,(2,'datediff(DD,''2016-07-29'',GetDate())')       -- System Functions
,(3,'(Select max(name) from master..spt_values)')  -- Select Value From Table
,(4,'convert(date,GetDate())')                     -- Get Today's Date


IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL
BEGIN
    DROP TABLE #Results
END
Create table #Results (ID int,Value varchar(max))

Declare @SQL varchar(max)=''
Select  @SQL = @SQL+concat(',(',ID,',cast(',Expression,' as varchar(max)))') From @Expression 
Select  @SQL = 'Insert Into #Results Select * From ('+Stuff(@SQL,1,1,'values')+') N(ID,Value)'
Exec(@SQL)

Select * From #Results

Declare @Var decimal(10,4) = (Select Value From #Results where ID=1)
Select @Var  -- 3.5400

Temp Table

ID  Value
1   3.54000000000000000
2   243
3   YES OR NO
4   2017-03-29
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
2

The last your solution fails because SET @v= @x.value('sql:variable("@calculatedDataString")', 'decimal(20,4)') does not evaluate the expression, it tries to cast @calculatedDataString to decimal which definitely must fail in most cases.

The only solution I know is CLR function. You may wish to look at this project https://github.com/zzzprojects/Eval-SQL.NET

It creates SQLNET UDT with methods you can use, kind of

SELECT  SQLNET::New(@calculatedDataString).EvalInt()

See https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-types/registering-user-defined-types-in-sql-server for how to register UDT in sql-server.

Serg
  • 22,285
  • 5
  • 21
  • 48
  • my 2 concerns 1- can i save this to a variable 2- can it be used inside a function – Mariam Mar 29 '17 at 19:35
  • Msg 243, Level 16, State 4, Line 6 Type SQLNET is not a defined system type. – Mariam Mar 29 '17 at 19:36
  • Yes, you need to install UDT from the above project in your DB first. And yes, its methods can be used in a function and to assign a value to a variable. – Serg Mar 29 '17 at 19:44
  • aha but i dont know how to install UDT i never worked on such installation – Mariam Mar 29 '17 at 20:02
  • Not so simple but doable. See the last link in the answer. – Serg Mar 29 '17 at 20:12
  • i am stuck with this microsoft explanation is there any other example on how to create udt – Mariam Mar 29 '17 at 21:01
  • when i Add a User-Defined Type class it is added as a .sql file not .cs file and cant rename it – Mariam Mar 29 '17 at 21:18
  • The project https://github.com/zzzprojects/Eval-SQL.NET doesn't contain binaries so you need to build it yourself. Download source code from the project page. See https://msdn.microsoft.com/en-us/library/a8s4s5dz(v=vs.100).aspx for more hints about deploying CLR UDT. – Serg Mar 30 '17 at 07:23
1

Here's my solution that's been in production for years. Amazing there isn't a provided solution given the power of RegEx.

This is using a safe CLR assembly which does presume some server level access. That means this might not work in some cases of AWS RDS, or anything other than managed instances of Azure SQL (like the new "server-less" option).

I'm presently testing an implementation of R language in an Azure SQL instance that has this very limitation. Thanks to: https://www.mssqltips.com/sqlservertip/4748/sql-server-2016-regular-expressions-with-the-r-language/

CREATE ASSEMBLY [TESTSCORING1_CLR_CS]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EE56E84B0000000000000000E00002210B010800000C00000006000000000000DE2B0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000902B00004B00000000400000B003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E40B000000200000000C000000020000000000000000000000000000200000602E72737263000000B00300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000C02B0000000000004800000002000500B4210000DC09000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040077000000010000110F00280F00000A2D067E1000000A2A0F00280F00000A6F1100000A0A72010000700672110000707215000070281200000A7221000070281300000A0B0772250000707229000070281200000A0B7E1000000A0C7E01000004076F1400000AA5160000016C281500000A0CDE09267E1000000A0CDE00082A000110000000005300196C00090100000142731600000A6F1700000A80010000042A1E02281800000A2A000000133004003D000000020000110F00280F00000A2C090F01280F00000A2D02142A0F00280F00000A6F1100000A0A0F01280F00000A6F1100000A0B060716176F1900000A6F1A00000A2A4A03027415000001510303506F1B00000A512A46027235000070281C00000A28040000062A00001330030021000000030000110F00280F00000A2D02142A0F00280F00000A6F1100000A0A0614176F1D00000A2A3A03027415000001281E00000A542A1E02281800000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000044030000237E0000B00300003C04000023537472696E677300000000EC0700003C000000235553002808000010000000234755494400000038080000A401000023426C6F620000000000000002000001571502000900000000FA013300160000010000001A000000030000000100000009000000090000001E0000000E00000003000000010000000400000000000A00010000000000060066005F000A00890078000E00BD00A8000E00C700A80006000801F5000600A20183010600C301B1010600DA01B1010600F701B10106001602B10106002F02B10106004802B10106006302B10106007E02B1010600970283010600AB02B1010600E402C40206000403C4020E005103360312009D037E030600AB035F000600C2035F000A00DC036D000A00E8036D00060017045F0006002A045F00000000000100000000000100010001001000220039000500010001000100100046003900050002000400310098000A005020000000009600D1000E000100F520000000008618EF0015000200E420000000009118D5036C000200002100000000960014011900020049210000000096002401220004005C2100000000960034012900060070210000000096004501290007009D21000000009600570130000800AC21000000008618EF0015000A00000001006601000001007101000002007501000001007F01020002007101000001007101000001007101000001007F0102000200AF013100EF0015003900EF0037004100EF0037004900EF0037005100EF0037005900EF0037006100EF0037006900EF0037007100EF0037007900EF003C008100EF0037008900EF0041009100EF0015009900EF001500210066034B00190070034F00090075034B00A100A3035300A900B20353001100B9035A001900C9035F00B900EF001500C100F00370000900EF001500A9000004C500A9000C04CC00A90012044B002100C903D800A9000C042301D100320430012000730046002E00230047012E002B004D012E00330035012E006B0082012E001B0047012E003B005A012E00630079012E00130035012E00430047012E0053004701800073007500C00073007500E0007300DE006500D3002C010480000001000000C60EFF5300000000000022030000020000000000000000000000010056000000000002000000000000000000000001006D000000000002000000000000000000000001009C000000000002000000000000000000000001005F00000000000000003C4D6F64756C653E005445535453434F52494E47315F434C525F43532E646C6C0041726974686D6574696343616C63756C6174696F6E73005445535453434F52494E473100537472696E6746756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E586D6C0053797374656D2E586D6C2E58506174680058506174684E6176696761746F72006E61760053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C446F75626C650053716C537472696E67004576616C7561746541726974686D657468696345787072657373696F6E002E63746F720053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650066537472696E67446C327461626C6500436861726C69737446696C6C526F770066537472696E6743646C327461626C650066537472696E67496E7473327461626C6500496E746C69737446696C6C526F770065787072657373696F6E007374720064656C696D6974657200726F770053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006E0053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005445535453434F52494E47315F434C525F4353004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C7565004E756C6C00546F537472696E670053797374656D2E546578742E526567756C617245787072657373696F6E73005265676578005265706C61636500537472696E6700436F6E636174004576616C7561746500446F75626C65006F705F496D706C69636974002E6363746F7200586D6C446F63756D656E7400586D6C4E6F6465004372656174654E6176696761746F7200546F4368617241727261790053706C6974005472696D00537472696E6753706C69744F7074696F6E7300436F6E7665727400546F496E743332000000000F6E0075006D00620065007200280000032F00000B200064006900760020000003290000032500000B20006D006F006400200000032C0000000000437A1F06B1F8A74D94BF2C5F099F89790008B77A5C561934E08903061209060001110D111103200001080002121511111111060002011C100E06000112151111060002011C1008042001010E0420010102042001010804010000000320000E0306110D0600030E0E0E0E0420011C0E050001110D0D0607030E0E110D0300000104200012094F01000200540E1146696C6C526F774D6574686F644E616D650F436861726C69737446696C6C526F77540E0F5461626C65446566696E6974696F6E144974656D4944206E76617263686172286D6178290620021D0308080620011D0E1D030407020E0E05000111110E4401000200540E1146696C6C526F774D6574686F644E616D650E496E746C69737446696C6C526F77540E0F5461626C65446566696E6974696F6E0A4974656D494420696E740820021D0E1D0311650307010E040001080E1101000C5445535453434F52494E473100000501000000000C010007436F6D7054656B00001E010019436F7079726967687420C2A920436F6D7054656B203230303600000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000B82B00000000000000000000CE2B0000002000000000000000000000000000000000000000000000C02B00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000580300000000000000000000580334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100FF53C60E00000100FF53C60E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004B8020000010053007400720069006E006700460069006C00650049006E0066006F00000094020000010030003000300030003000340062003000000030000800010043006F006D00700061006E0079004E0061006D0065000000000043006F006D007000540065006B00000044000D000100460069006C0065004400650073006300720069007000740069006F006E00000000005400450053005400530043004F00520049004E00470031000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700380032002E00320031003500300033000000000050001800010049006E007400650072006E0061006C004E0061006D00650000005400450053005400530043004F00520049004E00470031005F0043004C0052005F00430053002E0064006C006C0000005800190001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200043006F006D007000540065006B0020003200300030003600000000005800180001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400450053005400530043004F00520049004E00470031005F0043004C0052005F00430053002E0064006C006C0000003C000D000100500072006F0064007500630074004E0061006D006500000000005400450053005400530043004F00520049004E00470031000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700380032002E00320031003500300033000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700380032002E0032003100350030003300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO


CREATE FUNCTION [dbo].[EvaluateArithmethicExpression](@expression [nvarchar](4000))
RETURNS [float] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [TESTSCORING1_CLR_CS].[TESTSCORING1.ArithmeticCalculations].[EvaluateArithmethicExpression]
GO

-- Demo some simple evaluations
SELECT [dbo].[EvaluateArithmethicExpression]('1<10') as one, [dbo].[EvaluateArithmethicExpression]('1+1') as two, [dbo].[EvaluateArithmethicExpression]('300/100') as three
GO
rainabba
  • 3,804
  • 35
  • 35
  • The fact they haven't included any sort of Regular Expression in the Sql Server engine at this point is nigh on criminal! That is the ONE and only thing I still have any CLR assemblies left in the db for. – eidylon Feb 15 '21 at 22:17
0

Just for fun, I implemented a pure T-SQL function which does compute basic arithmetic (+ - / * ( ) and precedence). While I haven't benchmarked it, it's obvious that this solution will not be able to compete with CLR-based solutions. However, since it is pure T-SQL, it may be used in places where CLR cannot. This is not production-ready without adding lexical and syntax error handling code and testing it properly.

It's basically a single statement splitting the string into characters, then tokenizing these chars, and finally applying a shunting yard algorithm with two stacks stored as XML columns to compute the result.

CREATE FUNCTION fnEval(@s nvarchar(MAX))
RETURNS float
AS 
BEGIN
    -- Token Types:
    -- -1 => error
    -- 0 => whitespace
    -- 1 => number
    -- 2 => opening parens
    -- 3 => closing parens
    -- 4 => operator + -
    -- 5 => operator * /
    DECLARE @result float;
    WITH cteChar AS (
        SELECT 0 ix, CAST(N' ' AS nchar(1)) c, 0 iType, 1 iGroup -- Anchor
        UNION ALL 
        SELECT LEN(@s)+1, NULL, 3, -1 iGroup -- Finalizer
        UNION ALL
        SELECT c.ix+1, CAST(SUBSTRING(@s, c.ix+1, 1) AS nchar(1)), CASE 
            WHEN SUBSTRING(@s, c.ix+1, 1) LIKE CASE WHEN c.iType=1 and c.c=N'e' THEN N'[0123456789\+\-]' WHEN c.iType=1 THEN N'[0123456789.e]' ELSE N'[0123456789]' END ESCAPE N'\' THEN 1 
            WHEN SUBSTRING(@s, c.ix+1, 1)=N'(' THEN 2 
            WHEN SUBSTRING(@s, c.ix+1, 1)=N')' THEN 3 
            WHEN SUBSTRING(@s, c.ix+1, 1) IN (N'+', N'-') THEN 4
            WHEN SUBSTRING(@s, c.ix+1, 1) IN (N'*', N'/') THEN 5
            WHEN RTRIM(SUBSTRING(@s, c.ix+1, 1))=N'' THEN 0 
            ELSE -1 
        END, CASE 
            WHEN SUBSTRING(@s, c.ix+1, 1) LIKE CASE WHEN c.iType=1 and c.c=N'e' then N'[0123456789\+\-]' WHEN c.iType=1 THEN N'[0123456789.e]' END ESCAPE N'\' THEN c.iGroup 
            ELSE c.iGroup+1
        END
        FROM cteChar c 
        WHERE c.ix<LEN(@s)
    ), cteToken AS (
        SELECT CAST(ROW_NUMBER() OVER (ORDER BY MIN(c.ix)) AS int) ix, STRING_AGG(c.c, N'') WITHIN GROUP (ORDER BY c.ix) s, c.iType
        FROM cteChar c
        WHERE c.iType>0 -- We could handle lexical errors here
        GROUP BY c.iGroup, c.iType
    ), cteParser AS (
        SELECT CASE WHEN EXISTS (SELECT * FROM cteToken f WHERE f.ix>2) THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END bResult, t.ix+1 ixNext, CASE WHEN t.iType=1 THEN 
                (SELECT t.s [@val] FOR XML PATH(N'operand'), TYPE) 
            END xOperand, CASE WHEN t.iType>1 THEN 
                (SELECT t.s [@val], t.iType [@type] FOR XML PATH(N'operator'), TYPE) 
            END xOperator
        FROM cteToken t
        WHERE t.ix=1
        UNION ALL
        SELECT CASE WHEN p.xOperator.exist(N'/*')=0 AND t.s IS NULL THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END, t.ix+CASE WHEN (t.iType>3 AND t.iType<=p.xOperator.value(N'*[1]/@type', 'int')) OR (t.iType=3 AND NOT p.xOperator.value(N'*[1]/@type', 'int')=2) THEN 0 ELSE 1 END,
            CASE 
            WHEN t.iType=1 THEN 
                (SELECT t.s [@val], p.xOperand.query(N'*') FOR XML PATH(N'operand'), TYPE) 
            WHEN (t.iType>3 AND t.iType<=p.xOperator.value(N'*[1]/@type', 'int')) OR (t.iType=3 AND NOT p.xOperator.value(N'*[1]/@type', 'int')=2) THEN
                (SELECT CASE p.xOperator.value(N'*[1]/@val', 'nchar') 
                    WHEN N'+' THEN
                        p.xOperand.value(N'*[1]/*[1]/@val', 'float')+p.xOperand.value(N'*[1]/@val', 'float')
                    WHEN N'-' THEN
                        p.xOperand.value(N'*[1]/*[1]/@val', 'float')-p.xOperand.value(N'*[1]/@val', 'float')
                    WHEN N'*' THEN
                        p.xOperand.value(N'*[1]/*[1]/@val', 'float')*p.xOperand.value(N'*[1]/@val', 'float')
                    WHEN N'/' THEN
                        p.xOperand.value(N'*[1]/*[1]/@val', 'float')/p.xOperand.value(N'*[1]/@val', 'float')
                    END [@val], p.xOperand.query(N'*/*/*') FOR XML PATH(N'operand'), TYPE)
            ELSE
                p.xOperand
            END xOperand, 
            CASE 
            WHEN t.iType=1 THEN 
                p.xOperator
            WHEN (t.iType>3 AND t.iType<=p.xOperator.value(N'*[1]/@type', 'int')) OR (t.iType=3) THEN
                p.xOperator.query(N'/*/*')
            ELSE
                (SELECT t.s [@val], t.iType [@type], p.xOperator.query(N'*') FOR XML PATH(N'operator'), TYPE) 
            END xOperator
        FROM cteToken t
        JOIN cteParser p ON p.ixNext=t.ix AND p.bResult=CAST(0 AS bit)
    )
    SELECT @result=p.xOperand.value(N'/ *[1]/@val', 'float')
        FROM cteParser p
        WHERE bResult=CAST(1 AS bit)
        OPTION (MAXRECURSION 0);
    RETURN @result;
END
Lucero
  • 59,176
  • 9
  • 122
  • 152