Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 12746 | 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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones