Evaluate logical expressions in string column SQL
sql-server, sql-server-2005, t-sql
Solution
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.
Problem
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?