1

How can I mix boolean logic with bitwise and/or operators in SQL Server, specifically SQL Azure Database and/or SQL Server 2016?

To demonstrate what I'm trying to do, consider the following script:

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
--INSERT INTO @Data VALUES ((@Flag1 | @String LIKE '%qwerty%') & @Flag2)
SELECT * FROM @Data

The script works fine as-is but the moment you uncomment out that second INSERT, all hell breaks loose. I understand this is invalid syntax because I'm mixing boolean logic with bitwise operators. But what is the right way to get this logic in there? Without going crazy with CASE WHEN statements, is there some way to do this?

2

You may need to revise your code as follows:

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
INSERT INTO @Data VALUES ((@Flag1 | case when charindex('qwerty', @string ) > 0 then 1 else 0 end) & @Flag2)
SELECT * FROM @Data

Your original code has syntax error because LIKE is not an operator.

Another way is to use IIF as following

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
INSERT INTO @Data VALUES ((@Flag1 | iif( @String LIKE '%qwerty%', 1, 0)) & @Flag2)
SELECT * FROM @Data
  • Thanks! I think I like the IIF solution best of the two. – Jaxidian Dec 14 '16 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.