Re: Integer data type -> binary coded decimal

From: Steve Kass (skass_at_drew.edu)
Date: 03/20/04 

Date: Sat, 20 Mar 2004 12:00:05 -0500

Ali,

  While it doesn't answer your specific question, here is a routine to
decode base64 data. It will decode the data into separate 3-byte pieces
that you can merge into the original longer string. The one detail I
didn't bother with is to determine whether the final piece is one, two,
or three bytes - you do this by looking at the number of = characters at
the end of the base64 string. Hope this helps, though it's a little
unfinished.

-- you'll need a permanent table of integers:
create table N8000 (
  N int not null primary key
)
declare @i int
set @i = 0
while @i <= 8000 begin
  insert into N8000 values (@i)
  set @i = @i + 1
end
go

create table SampleData (
  [base64] varchar(400)
)
insert into SampleData values ('bXlwYXNzd29yZA==') -- "mypassword" encoded
go

-- Here's the decoding routine
select
  [base64],
  N/4 as Chunk,
  cast(sum(b*power(64,3-N%4)) as binary(3)) as Decoded
from (
  select
    [base64],
    N,
    case when c between 65 and 65+25 then c-65
         when c between 97 and 97+25 then c-71
         when c between 48 and 48+9 then c+4
         when c = ascii('=') then 0
         when c = ascii('+') then 62
         when c = ascii('/') then 63 end as b
  from (
    select
      [base64], N-1 as N, ascii(substring([base64],N,1)) as c
    from N8000, SampleData
    where N between 1 and len([base64])
  ) T
) T
group by [base64], N/4
order by [base64], N/4
go

-- Steve Kass
-- Drew University
-- Ref: 23DB840F-B9C7-4CCF-BD3C-49EDBEC8F0D0

Ali Cox wrote:

>I'm trying to create the binary coded decimal representation of a decimal integer, to be used/cast in a varchar concat with others of the same.
>Basically this is to decode MIME base64 encoding, and I have hit the proverbial brick wall with this part of it.
>
>EG. 34 (dec) is 100010 (bcd)
>
>I need to take the int 34, and change to the string '100010'.
>
>I hope there is a neat way to do this, perhaps mathematically?, as I don't really want to use a case statement. I can't see any datatype in SQL2K (Intel x86) that I can use.
>
>Thanks,
>Ali.
>
>

Relevant Pages

  • Re: Decoding Base64
    ... i am trying to decode a block of base64 into text, ... Richard Heathfield ... I've no idea what Richard has said else where, but his attitude to your posting here is hardly surprising. ... What do you think your local computer store would tell you if you took a printout of that code in and asked how to decode it? ...
    (comp.lang.c)
  • Re: Base64 partial decoding
    ... I have data encoded with base64. ... I read bytes from stream. ... decode this part of data and write to another stream. ... Now assuming if its practical and you know the exact structure of the stream you could use functions like chunk_split,unpack or even preg functions to get the correct data. ...
    (comp.lang.php)
  • Re: Base64
    ... | pass a base64 encoded string into and get back a decoded String. ... ** encodeencodes an arbitrary data block into MIME Base64 format string ... int encode(unsigned s_len, char *src, unsigned d_len, char *dst) ... ** DECODE BASE64 into RAW ...
    (comp.lang.c)
  • Re: Decoding Base64
    ... Richard Heathfield wrote: ... i am trying to decode a block of base64 into text, ... if i knew how it was encoded, i'd know how to decode it. ... i know nothing about this sort of computer speak. ...
    (comp.lang.c)