BIT_COUNT()¶

BIT_COUNT(N) returns the number of bits that are set to 1 in the argument N, or NULL if N is NULL. It handles integers, binary strings, hex literals, and floating-point types, using two’s complement representation for negative numbers.

Description¶

BIT_COUNT(N) counts the number of bits that are set to 1 in the argument N. The function supports a wide range of input types:

Integer types: TINYINT, SMALLINT, INT, BIGINT (signed and unsigned). For signed integers, the value is interpreted using two’s complement representation within the BIGINT range.
Floating-point types: FLOAT, DOUBLE, and DECIMAL. The value is first rounded to the nearest integer before counting bits.
String types: Nonbinary strings (CHAR, VARCHAR, TEXT) are converted to BIGINT before counting bits.
Binary strings: BINARY, VARBINARY, and hex literals (X'...', _binary 0x..., UNHEX(...)) count the set bits in each byte.

If N is NULL, the function returns NULL.

Syntax¶

> BIT_COUNT(N)

Arguments¶

Arguments	Description
N	Required. An integer, floating-point, string, or binary value whose set bits are to be counted.

Examples¶

DROP DATABASE IF EXISTS test_bit_count;
CREATE DATABASE test_bit_count;
USE test_bit_count;

-- Basic integer examples
SELECT BIT_COUNT(64) AS int_64, BIT_COUNT(BINARY 64) AS bin_64;
-- BIT_COUNT(0) = 0, BIT_COUNT(1) = 1, BIT_COUNT(2) = 1, BIT_COUNT(3) = 2, BIT_COUNT(255) = 8, BIT_COUNT(256) = 1
SELECT BIT_COUNT(0), BIT_COUNT(1), BIT_COUNT(2), BIT_COUNT(3), BIT_COUNT(255), BIT_COUNT(256);

-- NULL returns NULL
SELECT BIT_COUNT(NULL);

-- Negative numbers (two's complement)
SELECT BIT_COUNT(-1), BIT_COUNT(-2);

-- Unsigned integers
SELECT BIT_COUNT(CAST(255 AS TINYINT UNSIGNED)), BIT_COUNT(CAST(65535 AS SMALLINT UNSIGNED)), BIT_COUNT(CAST(4294967295 AS INT UNSIGNED));

-- Floating-point values (rounded to nearest integer)
SELECT BIT_COUNT(CAST(64.9 AS DOUBLE)), BIT_COUNT(CAST(255.9 AS DECIMAL(10, 1)));

-- Hex literals
SELECT BIT_COUNT(X'40'), BIT_COUNT(X'FF'), BIT_COUNT(X'FFFF');

-- Binary strings
SELECT BIT_COUNT(_binary X'40'), BIT_COUNT(_binary X'FF'), BIT_COUNT(_binary X'FFFF');

-- UNHEX function
SELECT BIT_COUNT(UNHEX('40')), BIT_COUNT(UNHEX('FF')), BIT_COUNT(UNHEX('FFFF'));

-- String conversion to BIGINT
SELECT BIT_COUNT('64'), BIT_COUNT('255'), BIT_COUNT('-1');

-- Table usage
CREATE TABLE t_bit_count(
    id INT,
    i BIGINT,
    u BIGINT UNSIGNED,
    s VARCHAR(10),
    b VARBINARY(4)
);
INSERT INTO t_bit_count VALUES
    (1, 64, 64, '64', X'40'),
    (2, -1, 255, '-1', X'FF'),
    (3, NULL, NULL, NULL, NULL),
    (4, 64, 64, '64', _binary 0x40);
SELECT id, BIT_COUNT(i), BIT_COUNT(u), BIT_COUNT(s), BIT_COUNT(b) FROM t_bit_count ORDER BY id;
DROP TABLE t_bit_count;

DROP DATABASE test_bit_count;

Notes¶

For signed integer types, BIT_COUNT() treats the value as a BIGINT two’s complement number. This means BIT_COUNT(-1) returns 64 (all bits set in a 64-bit two’s complement representation of -1).
When a nonbinary string is passed (e.g., '64'), it is first converted to a BIGINT value. An empty string or a string containing only whitespace converts to 0.
For binary strings (BINARY, VARBINARY, hex literals), each byte’s bits are counted individually, giving the total number of 1-bits across all bytes.
Values outside the BIGINT range are clamped: values greater than the maximum BIGINT are treated as the maximum BIGINT value, and values less than the minimum BIGINT are treated as the minimum BIGINT value.