Converting text to binary code - TSQL

I found a few threads on this using the search feature, but nothing for a purely T-SQL solution.

the need - A system is storing a weekly schedule as 0's and 1's in a string format to represent a week. 1 means yes, 0 means no....so 1100111 means sunday yes (first one), Monday yes (second 1), Tuesday no (the 0)...etc.

Short question - How do I go from an ascii char such as '>' to it's hex code '3E' and ultimately to it's binary '00111110' representation?

Long question - I'm extracting from a flat file system that stores a table as:

ID int,

priority_1 varchar(2)

...

It actually goes to priroity_128 (silly flat file), but I'm only interested in 1-7 and the logic for one should be easily reused for the others. I unfortunately have no control over this part of the extract. The values I get look like:

1 >

2 (edit, I actually put a symbol here that I receive from the system but the forum doesn't like.)

3 |

4 Y

I get the feeling these are appearing as their ascii chars because of the conversion as I extract.

select convert(varbinary,'>',2)

This returns 0x3E. The 0x part can be ignored... 3 in binary is 0011 and E is 1110...3E = 00111110. Trim the first 0 and it leaves the 7 bit code that I'm looking for. Unfortunately I have no idea how to express this logic here in T-SQL. Any ideas? I'm thinking as a function would be easiest to use...something like:

select id, binary开发者_如何学Pythonversionof(priority_1)

Here's a UDF that will convert from base-10 to any other base, including base-2...

Here's how you can use it:

SELECT YourDatabase.dbo.udf_ConvertFromBase10(convert(varbinary, '>', 2), 2)

Here's what it returns:

111110

And here's the function definition:

CREATE FUNCTION [dbo].[udf_ConvertFromBase10]
(
    @num INT, 
    @base TINYINT
)

RETURNS VARCHAR(255) 

AS 

BEGIN 

  -- Check for a null value.
  IF (@num IS NULL)
    RETURN NULL

  -- Declarations
  DECLARE @string VARCHAR(255)
  DECLARE @return VARCHAR(255)
  DECLARE @finished BIT
  DECLARE @div INT
  DECLARE @rem INT
  DECLARE @char CHAR(1)

  -- Initialize
  SELECT @string   = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
  SELECT @return   = CASE WHEN @num <= 0 THEN '0' ELSE '' END
  SELECT @finished = CASE WHEN @num <= 0 THEN 1 ELSE 0 END
  SELECT @base     = CASE WHEN @base < 2 OR @base IS NULL THEN 2 WHEN @base > 36 THEN 36 ELSE @base END

  -- Loop
  WHILE @finished = 0
  BEGIN

    -- Do the math
    SELECT @div = @num / @base
    SELECT @rem = @num - (@div * @base)
    SELECT @char = SUBSTRING(@string, @rem + 1, 1)
    SELECT @return = @char + @return
    SELECT @num = @div

    -- Nothing left?
    IF @num = 0 SELECT @finished = 1

  END

  -- Done
  RETURN @return

END

Your solution returns a string of a variable length. Not sure whether it was by design or you simply overlooked that fact.

Anyway, here's my solution, which always returns 7 0s or 1s:

CREATE FUNCTION fnIntTo7Bits (@Value int)
RETURNS varchar(7)
AS BEGIN
  DECLARE @Bits varchar(7);

  SELECT @Bits = COALESCE(@Bits, '') + CAST(CAST(@Value & number AS bit) AS varchar)
  FROM master..spt_values
  WHERE type = 'P' AND number IN (1, 2, 4, 8, 16, 32, 64)
  ORDER BY number DESC;

  RETURN @Bits;
END;

The master..spt_values table is a system table used internally but also accessible to the user. It seems to have been inherited from Sybase so it's a very old tool, which, to my mind, means it won't go too soon.

But if you like, you can use your own number table, which you don't even have to materialise, like this:

  ...
  SELECT @Bits = COALESCE(@Bits, '') + CAST(CAST(@Value & number AS bit) AS varchar)
  FROM (
    SELECT  1 UNION ALL SELECT  2 UNION ALL
    SELECT  4 UNION ALL SELECT  8 UNION ALL
    SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 64
  ) s (number)
  ORDER BY number DESC;
  ...

Answering my own question...though curious if anyone has something more elegant. I found this unsourced function using google:

CREATE FUNCTION udf_bin_me (@IncomingNumber int)
RETURNS varchar(200)
as
BEGIN

DECLARE @BinNumber  VARCHAR(200)
SET @BinNumber = ''

WHILE @IncomingNumber <> 0
BEGIN
    SET @BinNumber = SUBSTRING('0123456789', (@IncomingNumber % 2) + 1, 1) + @BinNumber
    SET @IncomingNumber = @IncomingNumber / 2
END

RETURN @BinNumber

END

Then use the Ascii function to get the char to it's ascii decimal value:

select dbo.udf_bin_me(ascii('>'))

Seems to be a bit of a run around, but I can work from that. Better solution anyone?

I just whipped this up, it maybe buggy... but it works:

DECLARE @value INT, @binary VARCHAR(10)
SELECT @value = ASCII('m'), @binary = ''
;WITH [BINARY] ([Location], [x], [BIT])
AS
(
    -- Base case
    SELECT 64, @value, @value % 2 
    UNION ALL
    -- Recursive
    SELECT [BINARY].[Location] / 2, [BINARY].[x] / 2, ([BINARY].[x] / 2) % 2   
    FROM [BINARY]
    WHERE [BINARY].[Location] >= 2
)
SELECT @binary = CAST([BIT] AS CHAR(1)) + @binary FROM [BINARY]

SELECT @binary