Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Evaluating Boolean expressions using T-SQL

By Eli Leiba,

Evaluating Boolean expressions using T-SQL

I wrote a procedure that can be used as a checking tools for evaluating Boolean expressions with up to 4 variable (A,B,C,D) logical operators NOT, AND ,OR are used. The evaluate Procedure gets a Boolean expression string with letters A, B, C, D and Boolean Operators AND, OR, NOT, also the number of parameters in equation and the values for each parameter separated by a dot sign.

The evaluation process replace each NOT with ~ sign, each AND with & sign and each OR with | sign. The values are parsed using the PARSENAME function and then replaced with the corresponding variable in the expression. TRUE is replaced by 1 and FALSE by 0.

The entire expression if the evaluated using dynamic T-SQL. If absolute value of result is 1 then expression is evaluated TRUE. Else a FALSE value is returned

Here is the code of this Procedure:

create proc evaluate ( @boolExp varchar(300), @numVars int, @vals varchar(30) )
as
begin

declare @varind int
declare @currval varchar(5)
declare @x int
declare @chrind int
Declare @res varchar(5)

set nocount on 
set @boolExp = replace (UPPER(@boolExp) ,'NOT','~')
set @boolExp = replace ((@boolExp) ,'OR','|')
set @boolExp = replace ((@boolExp) ,'AND','&')
set @varind = @numVars
set @chrind = 0

while @varind > 0
 begin 
  set @currval = ParseName (@vals,@varind)
  if @currval = 'true'
    set @boolExp = replace (@boolExp,char(65+@chrind),'1')
  else 
    set @boolExp = replace (@boolExp,char(65+@chrind),'0')
            
  set @varind = @varind - 1
  set @chrind = @chrind + 1
 end 

create table #temp (x int)
     
insert #temp exec ('select ' + @boolExp)
    
select @x = x from #temp
     
if (abs(@x) = 1) 
  set @res = 'TRUE' 
 else 
  set @res = 'FALSE'
      
drop table #temp
    
set nocount off
select @res
end
go

Example of using the procedure :

exec evaluate ' ( (NOT (A) AND B) OR (C AND NOT (B)) ) ',     3, 'FALSE.FALSE.TRUE'

exec evaluate ' ( (NOT (A) AND B) OR (C AND NOT (B)) ) ',     3, 'TRUE.FALSE.FALSE'

The results:
The first expression is evaluated to TRUE, and the second one to FALSE. The results are evaluated as follows:
First one:
  (NOT (FALSE) and FALSE) OR (TRUE AND NOT (FALSE)), which reduces to
  (TRUE and FALSE) OR (TRUE AND TRUE), which is
FALSE OR TRUE à  true

The second one becomes:
(not (TRUE) AND false) or (FALSE and NOT (FALSE), which becomes
(FALSE AND false) or (FALSE and TRUE, which is
(FALSE) or (FALSE) à  false

Author's Bio:

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail: iecdba@hotmail.com)

 
Total article views: 12780 | Views in the last 30 days: 4
 
Related Articles
ARTICLE

TSQL Challenge 45 - Evaluate mathematical expressions presented using

This challenge involves evaluating mathematical expressions presented using Roman Numerals and retur...

ARTICLE

TSQL Challenge 48 - Parse and evaluate Arithmetic Expressions using TS

This challenge is to parse and evaluate arithmetic expressions using TSQL.

FORUM

derived column --replace " with space expression error

derived column --replace " with space expression error

FORUM

Conditional operator evalutes both true & false expressions before evaluating condition

Causes an error from evaluating false expression when condition is true

FORUM

Expression

Expression

Tags
advanced querying    
t-sql    
 
Contribute