What Is the Use of "&" Operator in SQL Server

What is the use of & operator in SQL SERVER

& is bit AND operation:

• & Bitwise AND

• | Bitwise OR

• ^ Bitwise exclusive OR

• ~ Bitwise NOT

Ampersand (&) operator in a SQL Server WHERE Clause

& is the bitwise logical and operator - It performs the operation on 2 integer values.

WHERE (sc.Attributes & 1) = 0 

The above code checks to see if sc.Attributes is an even number. Which is the same as saying that the first bit is not set.

Because of the name of the column though: "Attributes", then the "1" value is probably just some flag that has some external meaning.

It is common to use 1 binary digit for each flag stored in a number for attributes. So to test for the first bit you use sc.Attributes&1, to test for the second you use sc.Attributes&2, to test for the third you use sc.Attributes&4, to test for the fourth you use sc.Attributes&8, ...

The = 0 part is testing to see if the first bit is NOT set.

Some binary examples: (== to show the result of the operation)

//Check if the first bit is set, same as sc.Attributes&1
11111111 & 00000001 == 1
11111110 & 00000001 == 0
00000001 & 00000001 == 1

//Check if the third bit is set, same as sc.Attributes&4
11111111 & 00000100 == 1
11111011 & 00000100 == 0
00000100 & 00000100 == 1

Is there a combination of LIKE and IN in SQL?

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference:

• Building Full-Text Search Applications with Oracle Text
• Understanding SQL Server Full-Text

What does & means in this sql where clause?

It's called a bitmask. It is used in situations where the individual bits in a number have different meanings, as opposed to a number just meaning the number itself (for instance, if you save your age to the database).

When you imagine any number in it's binary form and want to test if a certain bit in the number is set, you test it by using the binary AND operator with the number and the bit you want to test, like this:

if (number & 16 == 16)

In binary, this means the following (assuming, your number is 25):

if (00011001 & 00010000 == 00010000)

Here you can see, that the digits at the bit 5 (counted from the bottom up) are both 1, therefor the resulting number has a 1 at that bit. As there are no other 1s, the resulting number is 16 exactly when both numbers have a 1 at this position.

I would like to add: It's usually bad practice to encode different meanings into one database field. It's difficult to index and retrieve by an index, and depending on your DBMS might even be completely unindexed.

The difference between 'AND' and '&&' in SQL

For mySQL: The manual is not saying it explicitly, but they are listed as identical:

AND, &&

Logical AND. Evaluates to 1 if all operands are nonzero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

The operator precedence page also makes no distiction.

& operator usage in transact sql

& is bitwise AND (only 1 & 1 => 1):

LiveDemo

CREATE TABLE #tFlags(Flags INT);

INSERT INTO #tFlags VALUES (524675), (525698);

select *
       ,[bitwise AND] = CONCAT(Flags, '& 1 = ')
       ,[result]      = Flags & 1  
from #tFlags;

How it works:

000010000000000110000011   524675
000000000000000000000001   1       &
------------------------
000000000000000000000001   1

and:

000010000000010110000010   525698
000000000000000000000001   1       &
------------------------
000000000000000000000000   0   

The simple answer is:

• odd number & 1 = 1
• even number & 1 = 0

EDIT:

Number & 255: You can get rid of data except byte one.

00000001 00101100    300
00000000 11111111    255   &
-----------------
00000000 00101100    44

The point is you can treat binary number and bitwise operation as masking and use it to set/reset/xor value based on specific position.

How do I find ' % ' with the LIKE operator in SQL Server?

I would use

WHERE columnName LIKE '%[%]%'

SQL Server stores string summary statistics for use in estimating the number of rows that will match a LIKE clause. The cardinality estimates can be better and lead to a more appropriate plan when the square bracket syntax is used.

The response to this Connect Item states

We do not have support for precise cardinality estimation in the
presence of user defined escape characters. So we probably get a poor
estimate and a poor plan. We'll consider addressing this issue in a
future release.

An example

CREATE TABLE T
(
X VARCHAR(50),
Y CHAR(2000) NULL
)

CREATE NONCLUSTERED INDEX IX ON T(X)

INSERT INTO T (X)
SELECT TOP (5) '10% off'
FROM master..spt_values
UNION ALL
SELECT  TOP (100000)  'blah'
FROM master..spt_values v1,  master..spt_values v2

SET STATISTICS IO ON;
SELECT *
FROM T 
WHERE X LIKE '%[%]%'

SELECT *
FROM T
WHERE X LIKE '%\%%' ESCAPE '\'

Shows 457 logical reads for the first query and 33,335 for the second.

SQL LIKE operator not showing any result when it should

Promoted to an answer as requested.

You have some space characters in the end of your Owner field in Vehicle table. '=' operator skips trailing spaces, 'like' operator doesn't.

---

Related Topics