Convert bitwise int values into a Day of month mask

Question

I apologize if I seem unknowledgable. I have only been writing T-SQL for about 3 years now and most is self-taught. At my work we store Day of month values in bitmasks. eg. 1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx converts to 1, and 12345678910111213141516171819202122232425262728293031 converts to 2147483647. What I want/need to do is convert the int value into a varchar(64) containing numbers and 'x' characters. I have the following code from another post on here that kind of does something similar with some tweaking, but it provides the data backwards (right to left [as it should for binary] instead of left to right).

    declare @i int /* input */
    set @i = 42

    declare @result varchar(32) /* SQL Server int is 32 bits wide */
    set @result = ''
    while 1 = 1 begin
     select @result = convert(char(1), @i % 2) + @result,
           @i = convert(int, @i / 2)
     if @i = 0 break
    end

    select Replace(@result,'0','X')

It also does not provide all of the characters. All 31 characters must be backfilled.

I have also played with Rob Farley's Simple Recursive CTE for this.

"12345678910111213141516171819202122232425262728293031 converts to 2147483647" - How? Surely a bitmask would only be ones and zeros? — Phil, Mar 21 '12 at 10:26
@Phil the 53 character string `12345678910111213141516171819202122232425262728293031` has all 31 days of the month concatenated. `1111111111111111111111111111111` in binary = `2147483647` in decimal. — Martin Smith, Mar 21 '12 at 10:40

score 2 · Accepted Answer · edited May 23 '17 at 12:12

You could use a numbers table to split the @i into bits, then concatenate the bits into a varchar(32) value:

DECLARE @i int = 42;

WITH bits AS (
  SELECT
    number,
    B = CASE @i & POWER(2, number - 1) WHEN 0 THEN 'x' ELSE '1' END
  FROM master..spt_values
  WHERE type = 'P'
    AND number BETWEEN 1 AND 31
)
SELECT CAST((
  SELECT '' + B
  FROM bits
  ORDER BY number
  FOR XML PATH ('')
) AS varchar(32))

Result:

--------------------------------
x1x1x1xxxxxxxxxxxxxxxxxxxxxxxxx

This solution uses a system table called master..spt_values as a numbers table, but it's never a bad idea to generate and use your own instead.

Thanks! I apologize, I just saw this... I was not alerted to it being answered! I will try this at the first possible opportunity. — Jacrys, Apr 27 '12 at 04:40

Convert bitwise int values into a Day of month mask

1 Answers1