Base32 encoding and decoding functions in Microsoft T-SQL

base32.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. 
*/