Functions › Scalar Functions › Bit-Level Functions

Bit-Level Functions

Following is the syntax diagram for bit-level scalar functions:

►►─┬─ BIT_ADD(expression, expression) ─┬──────────────────────────────────────►◄
   ├─ BIT_AND(expression, expression) ─┤
   ├─ BIT_NOT(expression) ─────────────┤
   ├─ BIT_OR(expression, expression) ──┤
   └─ BIT_XOR(expression, expression) ─┘

The following rules define the input and output of each bit-level function, that is to say, all of these rules apply to every function.

• The expression that represents each parameter must resolve to INTEGER, SMALLINT, CHAR, or VARCHAR data. In this case, CA Datacom operates on character values of any length and rejects input parameters containing mixed data (mixed single-byte/double-byte). Though not required, we recommend using the FOR BIT DATA specification in the CREATE TABLE statement for columns that are going to use bit-level functions.
• For functions that receive two parameters, character and numeric parameters cannot be used in the same function call (numeric means INTEGER or SMALLINT in this context).
• The data type, length, and nullability of the result matches that of the input if there is either only one parameter or all parameters are of identical data type, length, and nullability. The attributes of the result are otherwise determined as follows:
  • The result is nullable (that is, it is not NOT NULL) if either of the parameters are nullable.
  • The length of the result matches the length of the longer parameter.
  • If both parameters are numeric, the result is INTEGER if at least one of the parameters is INTEGER, or SMALLINT if neither of the parameters is INTEGER.
  • If both parameters are character (VARCHAR is character) the data type of the result matches that of the first operand. This allows you to control the data type of the result when the data types of the parameters differ.

BIT_ADD(expression, expression)

This bit-level function returns the logical sum of the results of the input expressions. The logical sum is obtained by using logical addition and discarding any arithmetic overflow that is generated.

Following is an example of using BIT_ADD to construct a web address for a computer that is being turned on.

SELECT BIT_ADD(web_addrs_company_prefix,web_addrs_assigned_suffix) FROM web_addrses

Given input of

A web_addrs_company_prefix of 127.255.0.0 as an integer representation or 0x7FFF0000 in C-style notation and a web_addrs_assigned_suffix of 0.0.255.254 as an integer representation or 0x0000FFEE in C-style notation

Resulting output is

An integer representation of 127.255.255.254 or 0x7FFFFFFE in C-style notation

BIT_AND(expression, expression)

This bit-level function returns the logical AND of the results of the input expressions consisting of a (1) bit in the result for every input bit-pair of (1,1) and a (0) bit for all other bit-pair value combinations.

Note: A single 1 or 0 digit inside parentheses, that is a (1) or a (0), is used here to represent a single bit value. The term bit-pair refers to the value of a bit taken from the first input parameter and that of a bit taken from the corresponding position in the second input parameter. For example, a bit-pair of (1,0) refers to a 1 bit somewhere in the first parameter and a 0 bit in the corresponding position of the second parameter.

Following is an example of using BIT_AND to find what area of the web a browser is pointed to (that is, get the high-level portion of the web address).

SELECT BIT_AND(:bits_to_extract, web_addrs), user_name FROM web_addrses

Given input of

A bits_to_extract of 127.255.0.0 as an integer representation or 0x7FFF0000 in C-style notation, used to denote the bits you want to extract from the web address (here, bits_to_extract has a colon (:) in front of it to show it can be a host variable from a theoretical user program) and a web_addrs of 127.255.1.1 as an integer representation or 0x7FFF0101 in C-style notation

Resulting output is

An integer representation of 127.255.0.0 or 0x7FFF0000 in C-style notation

BIT_NOT(expression)

This bit-level function returns the logical NOT (the complement) of the results of the input expression. Each (1) bit in the input parameter becomes a (0) bit in the result, and each (0) bit becomes a (1) bit.

Note: A single 1 or 0 digit inside parentheses, that is a (1) or a (0), is used here to represent a single bit value.

Following is an example of using BIT_NOT to find out which status flags are not set.

 SELECT BIT_NOT(status_bits) from system_status

Given input of

A status_bits of 0x7F010101 in C-style notation

Resulting output is

0x80FEFEFE in C-style notation

BIT_OR(expression, expression)

This bit-level function returns the logical OR of the results of the input expressions. Each input bit-pair of (0,0) becomes a (0) bit in the result, and all other bit-pair value combinations become (1) bits.

Note: A single 1 or 0 digit inside parentheses, that is a (1) or a (0), is used here to represent a single bit value. The term bit-pair refers to the value of a bit taken from the first input parameter and that of a bit taken from the corresponding position in the second input parameter. For example, a bit-pair of (1,0) refers to a 1 bit somewhere in the first parameter and a 0 bit in the corresponding position of the second parameter.

Following is an example of using BIT_OR to construct a web address for a computer being turned on.

SELECT BIT_OR(web_addrs_company_prefix, web_addrs_assigned_suffix) from web_addrses

Given input of

A web_addrs_company_prefix of 127.255.0.0 in an integer representation or 0x7FFF0000 in C-style notation and a web_addrs_assigned_suffix of 0.0.255.254 in an integer representation or 0x0000FFFE in C-style notation

Resulting output is

An integer representation of 127.255.255.254 or 0x7FFFFFFE in C-style notation.

BIT_XOR(expression, expression)

This bit-level function returns the logical exclusive-OR of the results of the input expressions. Each bit-pair containing exactly one (1) bit becomes a (1) bit in the result. That is to say, each input bit-pair of (0,1) or (1,0) becomes a (1) bit in the result, and all other bit-pair value combinations, (0,0) or (1,1), become (0) bits.

Note: A single 1 or 0 digit inside parentheses, that is a (1) or a (0), is used here to represent a single bit value. The term bit-pair refers to the value of a bit taken from the first input parameter and that of a bit taken from the corresponding position in the second input parameter. For example, a bit-pair of (1,0) refers to a 1 bit somewhere in the first parameter and a 0 bit in the corresponding position of the second parameter.

Following is an example of using BIT_XOR to find out which system status flags do not match a required value.

 SELECT BIT_XOR(status_bits, required_status_bits) from system_status

Given input of

A status_bits of 0x7F010101 in C-style notation and a required_status_bits of 0x7F111110 in C-style notation

Resulting output is:

0x00101011 in C-style notation, giving the bits that do not match