Operatins and Functions for Bit Strings

This section describes operations and functions for bit strings, sequences of bit values, 0 and 1.

A bit string is a sequence of bits, each having the value of 0 or 1.

Operations involving bit strings are:

• Concatenation (the "||" operation) - Returns a bit string made by joining the specified strings.
• SUBSTRING - Returns a bit string made by extracting a portion of the specified string according to the specified position and the specified length.
• BIT_LENGTH - Returns a number indicating the number of bits of the specified string.
• Comparison (the ">" or "<" operation) - Returns true or false by comparing each bit of the first operand with the bit of the second operand at the same position according to a predefined collating sequence.
• "=" operation - Returns true if each bit of the first operand is equal to the bit of the second operand at the same position according to a predefined collating sequence.
• POSITION - Returns a number indicating the position of a short string inside a long string.

Examples of bit strings tested on MySQL only, BitStringOps.sql:

```-- BitStringOps.sql
--
SELECT BINARY('Hello'); -- BINARY() is not part of SQL-92
SELECT BINARY(X'41424344');
-- SELECT CONCAT(X'4',X'1'); -- X'..' takes double hex digits only
-- SELECT SUBSTRING(BINARY(X'41424344'),16,32); -- Not at bit level
SELECT LENGTH('Hello');
SELECT BIT_LENGTH('Hello');
SELECT 'xyz ' = 'XYZ';
SELECT BINARY('xyz') = BINARY('XYZ');
```

Run BitStringOps.sql on MySQL 5.7 server, you will get:

```C:\herong>set "MySQL=\Program Files\MySQL\MySQL Server 5.7"
< BitStringOps.sql
BINARY('Hello')
Hello
BINARY(X'41424344')
ABCD
SUBSTRING(BINARY(X'41424344'),16,32)

LENGTH('Hello')
5
BIT_LENGTH('Hello')
40
'xyz ' = 'XYZ'
1
BINARY('xyz') = BINARY('XYZ')
0
```

A number of interesting things to note here:

• MySQL seems to not taking B'10000001' as X'41'. So you can not really enter a bit string that is not 8*n bits long.
• BINARY() is a MySQL function. I can not find it in SQL-92.
• X'..' takes only 2*n hex digits.
• SUBSTRING() works at character string level, not at bit string level.
• '=' operator works at binary level as well as character level depending on the context.

Last update: 2015.