Evaluate logical expressions in string column SQL

Question

I have a table containing columns id(int), logical expression(varchar) and result(bit). The logical expression is stored in a varchar which I need to evaluate and put the result into the result column. For example, the column could contain:

'1=1'
'2<3 AND 1^1=1'
'3>4 OR 4<2'

The result column should then contain

1
0
0

Currently I am using a cursor to navigate the rows and using dynamic sql to evaluate the expression.

"IF(" + @expression + ") SET @result = 1" 

Is there a better, more efficient way to do this? I would ideally like to get rid of the cursor. Any ideas? Would this be better performed using an assembly?

Answer 1

I'd go with a CLR.

I posted a very similar answer here: Convert string with expression to decimal

infact, the above answer would work fine unmodified for (and any other simple expressions):

SELECT dbo.eval('1=1' )
SELECT dbo.eval('3>4 OR 4<2' )

However, it would fail for the one using the ^ (caret) operator - you would need to tweak the CLR to handle the bitwise XOR.

Answer 2

Some time ago, I wrote a user-defined function in SQL to give the decimal result of evaluating infix arithmetic expressions like 1+2+3+4/(5-2). The code is here. You could probably adapt it to work for your boolean expressions. It uses a table of integers called Sequence0_8000, which you can populate in any way you want.