Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What does the pipe operator do in SQL?

I am redeveloping an application and have found this sql statement, what does the | character do in this part (au.ExNetBits | 8), I haven't seen before and can't find any answer online?

SELECT au.AccountID,au.ExNetBits FROM AccountUser au (NOLOCK)
WHERE au.CDAGUserId=102 and (au.ExNetBits | 8) = au.ExNetBits

like image 422
Anthony Kallay Avatar asked Sep 26 '13 19:09

Anthony Kallay


People also ask

What does || mean in SQL query?

Concatenation Operator. ANSI SQL defines a concatenation operator (||), which joins two distinct strings into one string value.

What is pipe symbol in SQL?

Pipe Character (|) is the Bitwise OR Operator in T-SQL The pipe character (|) is the bitwise OR operator. The query below produces the truth table for OR operations between the AttributeA and AttributeB columns. The LogicalOR will be 1 if any either AttributeA or AttributeB equals 1.

What do double pipes mean in SQL?

As Chris mentioned, this is PL/SQL SQL, like Oracle. If your code is Correct Double pipe sign"||" means Concatnation, otherwise it may chanche to single pipe symbol "|" which means Bitwise OR.

What is pipe symbol in Oracle?

The || operator is used for concatenating two strings, in Oracle a single | is not a valid operator.


1 Answers

from the MSDN documentation,

| (Bitwise OR) (Transact-SQL)

Performs a bitwise logical OR operation between two specified integer values as translated to binary expressions within Transact-SQL statements.

...

The bitwise | operator performs a bitwise logical OR between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either or both bits (for the current bit being resolved) in the input expressions have a value of 1; if neither bit in the input expressions is 1, the bit in the result is set to 0.

If the left and right expressions have different integer data types (for example, the left expression is smallint and the right expression is int), the argument of the smaller data type is converted to the larger data type. In this example, the smallint expression is converted to an int.

for example, see this fiddle,

SELECT 1 | 1, 1 | 2, 2 | 4, 3 | 5;

outputs

1    3    6    7

to explain this behavior you must consider the bit patterns of the operands,

1 | 1

  00000001  = 1
| 00000001  = 1
_______________
  00000001  = 1

1 | 2

  00000001  = 1
| 00000010  = 2
_______________
  00000011  = 3

2 | 4

  00000010  = 2
| 00000100  = 4
_______________
  00000110  = 6

3 | 5

  00000011  = 3
| 00000101  = 5
_______________
  00000111  = 7
like image 5
Jodrell Avatar answered Oct 17 '22 18:10

Jodrell