T-SQL中如何将INT类型数据转换为布尔数组？

Got it, let's break this down and fix your problem!

Why Your Current Approach Isn't Working #

When you run SELECT CAST(15 AS binary(5)), SQL stores the integer as byte-level data, not individual bits. The integer 15 translates to the hex value 0xF (a single byte), so binary(5) pads it with four leading zero bytes to reach the 5-byte length—hence the result 0x0000000F. This doesn't give you the bit-by-bit breakdown you need.

The Fix: Bitwise Operations to Extract Individual Bits #

To get the 5-bit boolean array you want ([1,1,1,1,0]), you'll use bitwise AND (&) to check if each bit in the integer is set to 1. Here's how to do it in common SQL databases:

For PostgreSQL

PostgreSQL has native array support, so you can directly construct the boolean array:

SELECT ARRAY[
  CASE WHEN 15 & POWER(2, 0) > 0 THEN 1 ELSE 0 END, -- Check 2^0 = 1 (1st bit)
  CASE WHEN 15 & POWER(2, 1) > 0 THEN 1 ELSE 0 END, -- Check 2^1 = 2 (2nd bit)
  CASE WHEN 15 & POWER(2, 2) > 0 THEN 1 ELSE 0 END, -- Check 2^2 = 4 (3rd bit)
  CASE WHEN 15 & POWER(2, 3) > 0 THEN 1 ELSE 0 END, -- Check 2^3 = 8 (4th bit)
  CASE WHEN 15 & POWER(2, 4) > 0 THEN 1 ELSE 0 END  -- Check 2^4 = 16 (5th bit)
] AS boolean_array;

This returns {1,1,1,1,0} exactly as you need.

For SQL Server

SQL Server uses JSON arrays for this kind of structure, or you can return individual columns and combine them:

SELECT JSON_QUERY('[' + 
  CAST(CASE WHEN 15 & 1 > 0 THEN 1 ELSE 0 END AS VARCHAR) + ',' +
  CAST(CASE WHEN 15 & 2 > 0 THEN 1 ELSE 0 END AS VARCHAR) + ',' +
  CAST(CASE WHEN 15 & 4 > 0 THEN 1 ELSE 0 END AS VARCHAR) + ',' +
  CAST(CASE WHEN 15 & 8 > 0 THEN 1 ELSE 0 END AS VARCHAR) + ',' +
  CAST(CASE WHEN 15 & 16 > 0 THEN 1 ELSE 0 END AS VARCHAR) +
']') AS boolean_array;

This returns the JSON array [1,1,1,1,0].

For MySQL

MySQL also supports JSON arrays directly:

SELECT JSON_ARRAY(
  IF(15 & 1, 1, 0),
  IF(15 & 2, 1, 0),
  IF(15 & 4, 1, 0),
  IF(15 & 8, 1, 0),
  IF(15 & 16, 1, 0)
) AS boolean_array;

This will output the desired array [1,1,1,1,0].

How This Works #

• The bitwise AND (&) compares each bit of the integer with a power of 2 (e.g., 15 & 8 checks if the 4th bit is set).
• If the result is greater than 0, that bit is 1—we map this to 1 in our array; otherwise, we use 0.

内容的提问来源于stack exchange，提问作者greenheadprogrammer