Operations and Functions for Bit Strings

MySQL Tutorials - Herong's Tutorial Examples

∟Operations and Functions for Bit Strings

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
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
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 8.0 server, you will get:

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < 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.