How Check string of Boolean expression is return true or false
-
Hi Guys,
I have a requirement in my project is how to check string of boolean expression is true or false.
Example
Declare @strBoolean nvarchar(1000)
set @strBoolean ='(1=1 AND (1=1 OR 1=1) || (1=1 AND 1=0) )
I want to check @strBoolean is true or false
ie if(@strBoolean)
print('true')
else
print('false')
Please help me to solve the problem
Thanks
Siv
-
Hi
If @strBoolean = 1 BEGIN
Print 'True'
End else begin
If @strBoolean= 0 BEGIN
Print 'False'
End else begin
Print 'Null'
End
End
Should do it...
Time to make a change -
Is this SQL Server code? || is not a valid logical operator in T-SQL. Why do you want SQL Server to evaluate a passed in logic statement in this way? Surely doing that evaluation in the application language you're planning to pass it to SQL Server from would be more efficient rather than doing a roundtrip to the database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I'd suggest doing that in application code, not database code. They're more designed for that kind of work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You can evaluate the expression by using dynamic SQL.
There's no double-pipe in SQL. Usually X || Y means X OR Y except that Y is not evaluated if X is false. Obviously, you know the logic required so I'll just use some simplified examples.
DECLARE
@strBoolean NVARCHAR(1000),
@strSQL NVARCHAR(1000),
@bResult BIT = 0
SET @strBoolean = N'(1=1 AND (1=1 OR 2=2))' --returns 1
--SET @strBoolean = N'(1=2 AND (1=1 OR 2=2))' --returns 0
SET @strSQL = N'SELECT @bCheck = 1 WHERE '+@strBoolean
EXEC sp_executesql @strSQL, N'@bCheck INT OUT', @bResult OUT
SELECT
(CASE @bResult
WHEN 1 THEN 'True'
ELSE 'False'
END) result
-
HI Steven Willis,
Thank you very much Steven, Your solution solved my problem.
Thanks,
Siv
-
Thanks for your solution.
But it does not works with string logical expression like below
'FirstName=FirstName AND LastName=LastName'
Please provide me a solution that works with with varchar values as well...
without adding single quotes in column like below
''FirstName''=''FirstName'' AND ''LastName''=''LastName''
otherwise it will works fine..
Thanks Again!!
-
Hello and thank you @SSCrazy Eights,
I would like to see if SQL has the same problem as Python.
In Python if you declare '@a' and '@b' as two variables of value '100' and you compare them you have a TRUE. But if you compare the same two variable with value '1000' you will have a FALSE.
I't crazy, right?
This because Python has some cache logic that goes up till a certain numeric limit and then it just returns you a false.I'm trying to archive the same with SQL and I would like to test a few data type so I changed your code to this:
DECLARE @a numeric set @a = 100
DECLARE @b-2 numeric set @b-2 = 100DECLARE
@strBoolean NVARCHAR(1000),
@strSQL NVARCHAR(1000),
@bResult BIT = 0SET @strBoolean = N'('@a'='@b' AND ('@a'='@b' OR '@a'='@b'))' --returns 1
--SET @strBoolean = N'(1=2 AND (1=1 OR 2=2))' --returns 0SET @strSQL = N'SELECT @bCheck = 1 WHERE '+@strBoolean
EXEC sp_executesql @strSQL, N'@bCheck INT OUT', @bResult OUTSELECT
(CASE @bResult
WHEN 1 THEN 'True'
ELSE 'False'
END) resultbut SSMS returns me a:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@a'.Is there a way to execute the variables inside the "SET @strBoolean"?
Thank you -
This article explains it better than I do, read the reply by Peter Varshavsky:
"[...] It turns out (in Python 2.7.9) that integers from -5 to 256 are stored in an array in memory, so when you assign an integer value in that range to a variables, the variables point to the same location in memory. However for integers outside of that range new memory is allocated, and the id is different."
I also discovered that the same logic applies to Java.
So I want to prove is the same case applies to '==' and 'IS' in SQL. Something like this:
DECLARE @a numeric set @a = 100
DECLARE @b-2 numeric set @b-2 = 100IF @a == @b-2 PRINT 'True' ELSE PRINT 'False'
IF @a IS @b-2 PRINT 'True' ELSE PRINT 'False'
Which unfortunately still returns me:Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='. - francesco.mantovani - Sunday, December 16, 2018 1:18 PM
It's not working due to syntax errors in your concatenation. Frankly you're doing it the hardway since you don't HAVE to concatenate. Just pass the variables in to the dynamic SQL.
DECLARE @a numeric set @a = 100
DECLARE @b-2 numeric set @b-2 = 100DECLARE @strBoolean NVARCHAR(1000),
@strSQL NVARCHAR(1000),
@bResult BIT = 0SET @strBoolean = N'(+@a=@b AND (@a=@b OR @a=@b))' --returns 1
--SET @strBoolean = N'(1=2 AND (1=1 OR 2=2))' --returns 0SET @strSQL = N'SELECT @bCheck = 1 WHERE '+@strBoolean
EXEC sp_executesql @strSQL, N'@a int, @b-2 int,@bCheck INT OUT' , @a,@b-2, @bResult OUTSELECT
(CASE @bResult
WHEN 1 THEN 'True'
ELSE 'False'
END) result----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? - francesco.mantovani - Sunday, December 16, 2018 1:25 PM
Neither of those syntax options are valid. The only valid use of IS that I know of in SQL is "IS NULL" so the form cannot be generalized to other uses.
SQL is not a general purpose programming language per se, so trying to do apples to apples comparisons with other programming languages seems a bit odd. What are you hoping to do with these kinds of findings or proofs?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Hello Matt and thank you for you interest in my question.
This is what I have in Python:
Basically with "==" Python is comparing two variables but while using "IS" Python is actually reading the memory space, so the 0100101001..... and as they are not where they are supposed to be, because they are cached somewhere, the bits are not matching.
I think you are right saying that SQL is not a general purpose programming so such issue might not affect SQL. But I'm a maniac of the Proof Of Concept and I would like to try it out on SSMS using all available data type.
So you will help me if you tell me how to build a simple Boolean query that compares an "X" to an "Y".
Thank you. -
See here for an explanation of the differences between '==' and 'is' in python.
There is nothing similar in SQL
- francesco.mantovani - Wednesday, January 2, 2019 1:52 PMHello Matt and thank you for you interest in my question.
This is what I have in Python:
Basically with "==" Python is comparing two variables but while using "IS" Python is actually reading the memory space, so the 0100101001..... and as they are not where they are supposed to be, because they are cached somewhere, the bits are not matching.
I think you are right saying that SQL is not a general purpose programming so such issue might not affect SQL. But I'm a maniac of the Proof Of Concept and I would like to try it out on SSMS using all available data type.
So you will help me if you tell me how to build a simple Boolean query that compares an "X" to an "Y".
Thank you.SQL isn't intended to cover the same gamut as PYTHON, Java, C# or others, so no you likely will not find the same issues. As is often described here, "general purpose" programming languages most often are procedural, imperative languages, where you are forced to spell out HOW to do something in order for the goal to be achieved. SQL on the other hand tends to be more declarative in that it works best when you specify WHAT you need and let the engine determine HOW to get the information you want. While it does have some procedural facilities, it tends to work best when you don't second-guess the DB engine, so the common recommendation is to avoid those procedural constructs unless you have a very good reason not to. As a more specific example - you will have a VERY hard time having any form of control over whether the engine is using memory or disk space or cache, since that's the purview of the various components of the DB engine.As to your second question on comparison of 2 values - just simply use single operators. Assignment uses the SET command, so there's no ambiguity as to whether you're assigning or performing a comparison.
DECLARE @a numeric set @a = 100
DECLARE @b-2 numeric set @b-2 = 100IF @a = @b-2
PRINT 'True'
ELSE
PRINT 'False'IF @a IS NULL
PRINT 'This is null'
ELSE
PRINT 'This is not null'As mentioned before, IS isn't a general purpose construct in SQL, so the IS NULL clause is where you will find it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Ahhhhhhh!!!
In fact there is no "IS" in T-SQL but only "IS NULL" or "IS NOT NULL"
It all makes sense now.
Thank you guys.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply