Skip to content

Instantly share code, notes, and snippets.

@rmalayter
Last active June 13, 2017 01:03
Show Gist options
  • Save rmalayter/3976537 to your computer and use it in GitHub Desktop.
Save rmalayter/3976537 to your computer and use it in GitHub Desktop.
Base32 encoding and decoding functions in Microsoft T-SQL
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[base32enc]')
AND type IN (
N'FN'
,N'IF'
,N'TF'
,N'FS'
,N'FT'
)
)
DROP FUNCTION [dbo].[base32enc]
GO
CREATE FUNCTION [dbo].[base32enc] (
@x VARBINARY(max)
,@pad INT = 1
)
RETURNS VARCHAR(max)
AS
BEGIN
/* RFC 4648 compliant BASE32 encoding function, takes varbinary data to
encode as first parameter, and then a 0 or 1 to indicate whether or not
padding characters (equals signs) should be included in the output. */
DECLARE @p INT
DECLARE @c BIGINT
DECLARE @s BIGINT
DECLARE @q BIGINT
DECLARE @t BIGINT
DECLARE @o VARCHAR(max)
DECLARE @op VARCHAR(8)
SET @o = ''
SET @p = DATALENGTH(@x) % 5 --encode with 40-bit blocks
IF @p <> 0
SET @x = @x + SUBSTRING(0x0000000000, 1, 5 - @p)
SET @c = 0
WHILE @c < DATALENGTH(@x)
BEGIN
SET @s = 0
SET @t = CAST(SUBSTRING(@x, @c + 1, 5) AS BIGINT)
SET @op = ''
WHILE @s < 8
BEGIN
SET @q = @t % 32
SET @op = CASE
WHEN @q BETWEEN 0
AND 25
THEN CHAR(@q + 65)
ELSE CHAR(@q + 24)
END + @op
SET @t = @t / 32
SET @s = @s + 1
END
SET @o = @o + @op
SET @c = @c + 5
END
DECLARE @padc CHAR(1)
--padding section
SET @padc = CASE
WHEN @pad IS NULL
OR @pad = 1
THEN '='
ELSE ''
END
SET @o = CASE
WHEN @p = 1
THEN SUBSTRING(@o, 1, DATALENGTH(@o) - 6) + REPLICATE(@padc, 6)
WHEN @p = 2
THEN SUBSTRING(@o, 1, DATALENGTH(@o) - 4) + REPLICATE(@padc, 4)
WHEN @p = 3
THEN SUBSTRING(@o, 1, DATALENGTH(@o) - 3) + REPLICATE(@padc, 3)
WHEN @p = 4
THEN SUBSTRING(@o, 1, DATALENGTH(@o) - 1) + REPLICATE(@padc, 1)
ELSE @o
END
RETURN LTRIM(RTRIM(@o))
END
GO
GRANT EXECUTE
ON [dbo].[base32enc]
TO PUBLIC
GO
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[base32dec]')
AND type IN (
N'FN'
,N'IF'
,N'TF'
,N'FS'
,N'FT'
)
)
DROP FUNCTION [dbo].[base32dec]
GO
CREATE FUNCTION [dbo].[base32dec] (@x VARCHAR(max))
RETURNS VARBINARY(max)
AS
BEGIN
/* RFC 4648 compliant BASE32 decoding function, takes varchar data to decode as only parameter*/
DECLARE @p INT
DECLARE @c BIGINT
DECLARE @s BIGINT
DECLARE @q BIGINT
DECLARE @t BIGINT
DECLARE @o VARBINARY(max)
SET @o = CAST('' AS VARBINARY(max))
SET @p = 0 --initialize padding character count
--we can strip off padding characters since BASE32 is unambiguous without them
SET @x = REPLACE(@x, '=', '')
SET @p = DATALENGTH(@x) % 8 --encode with 40-bit blocks
IF @p <> 0
SET @x = @x + SUBSTRING('AAAAAAAA', 1, 8 - @p)
SET @x = UPPER(@x)
SET @x = REPLACE(@x, '1', 'I')
SET @x = REPLACE(@x, '0', 'O')
SET @c = 1
WHILE @c < DATALENGTH(@x) + 1
BEGIN
SET @s = 0
SET @t = 0
WHILE @s < 8 --accumulate 8 characters (40 bits) at a time in a bigint
BEGIN
SET @t = @t * 32
SET @t = @t + CASE
WHEN SUBSTRING(@x, @c, 1) BETWEEN 'A'
AND 'Z'
THEN ASCII(SUBSTRING(@x, @c, 1)) - 65
WHEN SUBSTRING(@x, @c, 1) BETWEEN '2'
AND '7'
THEN ASCII(SUBSTRING(@x, @c, 1)) - 24
ELSE 0
END
SET @s = @s + 1
SET @c = @c + 1
END
SET @o = @o + SUBSTRING(CAST(@t AS BINARY (8)), 4, 5)
END
--remove padding section
SET @o = CASE
WHEN @p = 2
THEN SUBSTRING(@o, 1, DATALENGTH(@o) - 4)
WHEN @p = 4
THEN SUBSTRING(@o, 1, DATALENGTH(@o) - 3)
WHEN @p = 5
THEN SUBSTRING(@o, 1, DATALENGTH(@o) - 2)
WHEN @p = 7
THEN SUBSTRING(@o, 1, DATALENGTH(@o) - 1)
ELSE @o
END
RETURN @o
END
GO
GRANT EXECUTE
ON [dbo].[base32dec]
TO PUBLIC
GO
/*
Copyright © 2012 Ryan Malayter. All Rights Reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are
met:
1. Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
documentation and/or other materials provided with the distribution.
3. The name of the author may not be used to endorse or promote products
derived from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY Ryan Malayter "AS IS" AND ANY EXPRESS OR
IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment