Converting text to binary code - TSQL

Question

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, binaryversionof(priority_1)

Might be easiest just to create a table with the 256 possibilities. (or 128 if you are assuming that the first bit is always zero). How does the system cope with 0x00000000 (that is the NULL character and I can imagine that might cause additional issues). — Martin Smith
– Martin Smith, Commented Mar 25, 2011 at 19:17
That was my first thought, but I was thinking a UDF would be preferable over a reference table. Hmmm, I'll have to consider the possibility of having a reference table directly translate '>' to it's Sunday - Sat bit version. Got me thinking on another alternative — Twelfth
– Twelfth, Commented Mar 25, 2011 at 19:30
Second part - for some reason, the flat file I'm working with marks nulls as ÿ, which I recognize as 0xFF or 11111111. Makes the null issue easier to handle — Twelfth
– Twelfth, Commented Mar 25, 2011 at 19:46
The lookup/reference table should be faster than the function call. For small or infrequent calls, it'll be irrelevant, but for serious workloads, well, SQL as designed will work better with tables than with procedural code. — Philip Kelley
– Philip Kelley, Commented Mar 25, 2011 at 19:46
Good point Philip Kelley (and nice All your base pic :P). The reference table also solves the problem of how to convert this horrible priority_1 to priority_128 unnormalized table into something more workable...might be the better alternative due to that. Still working, but this appears to be the route I'll take...the UDF works, but this thing will spit out some 10k records at me daily, making the linear route less desireable. heh, this forum always makes for a great reality check, ty you 2. — Twelfth
– Twelfth, Commented Mar 25, 2011 at 20:11

Michael Fredrickson · Accepted Answer · 2011-03-25 19:23:10Z

2

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:

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

answered Mar 25, 2011 at 19:23

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Twelfth Over a year ago

Definately looks like it'll work...looks like a more fleshed out version (can represent data in any base from base10) then what I just posted. Thanx!

Andriy M · Accepted Answer · 2011-03-27 13:03:47Z

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;
  ...

Twelfth · Accepted Answer · 2011-03-25 19:26:57Z

0

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?

answered Mar 25, 2011 at 19:26

Twelfth

7,2083 gold badges30 silver badges35 bronze badges

Comments

SQLMason · Accepted Answer · 2011-03-25 20:32:46Z

0

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

edited Mar 25, 2011 at 20:32

answered Mar 25, 2011 at 20:27

SQLMason

3,2851 gold badge32 silver badges40 bronze badges

2 Comments

Martin Smith Over a year ago

The question is tagged SQL Server 2000

SQLMason Over a year ago

ah crud (no phun intended). I was looking through TSQL and SQL Server. Please don't -1 as it is a cool way to use a CTE to get the binary (IMHO).

Collectives™ on Stack Overflow

Converting text to binary code - TSQL

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related