MIME64 Encoder and Decoder written in T-SSQL

Peter Larsson, 2006-11-09 (first published: 2006-06-15)

Here is a MIME64 encoder function written entirely in T-SQL!
© 2006 Peter Larsson, Developer Workshop, all rights reserved

As long as the copyright notice is visible within the function declaration
and you include a note in the documentation of your system that these
functions are written by me, you may use these functions for free of charge.

If you intend to use these functions in a commercial application, you are
also required to email me about your system and provide an emailaddress to
the application manager.

If you like the code and use it just for fun, send an e-mail to me and tell
me about your experience with these functions.

These two functions encodes data to MIME64 compliant text and decodes and MIME64 compliant text back to data.

    CREATE FUNCTION dbo.fnMIME64Encode
(
	@PlainText VARCHAR(6000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
-- © 2006 Peter Larsson, Developer Workshop, all rights reserved

	DECLARE	@Characters VARCHAR(64), @Index SMALLINT,
		@m1 TINYINT, @m2 TINYINT, @m3 TINYINT, @m4 TINYINT,
		@Mime VARCHAR(8000), @FinalBlock TINYINT

	SELECT	@Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
		@FinalBlock = DATALENGTH(@PlainText) % 3,
		@PlainText = @PlainText + REPLICATE(CHAR(0), (3 - DATALENGTH(@PlainText) % 3) % 3),
		@Index = DATALENGTH(@PlainText) - 2,
		@Mime = ''

	WHILE @Index > 0
		BEGIN
			SELECT	@m1 = (ASCII(SUBSTRING(@PlainText, @Index, 1)) & 252) / 4,
				@m2 = (ASCII(SUBSTRING(@PlainText, @Index, 1)) & 3) * 16 + (ASCII(SUBSTRING(@PlainText, @Index + 1, 1)) & 240) / 16,
				@m3 = (ASCII(SUBSTRING(@PlainText, @Index + 1, 1)) & 15) * 4 + (ASCII(SUBSTRING(@PlainText, @Index + 2, 1)) & 192) / 64,
				@m4 = ASCII(SUBSTRING(@PlainText, @Index + 2, 1)) & 63
 
			SELECT	@Mime =	CASE
						WHEN @FinalBlock = 1 THEN SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + '=' + '='
						WHEN @FinalBlock = 2 THEN SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + SUBSTRING(@Characters, @m3 + 1, 1) + '='
						ELSE SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + SUBSTRING(@Characters, @m3 + 1, 1) + SUBSTRING(@Characters, @m4 + 1, 1)
					END + @Mime,
				@Index = @Index - 3,
				@FinalBlock = 0
		END

	RETURN @Mime
END

and of course the MIME64 decoder function

CREATE FUNCTION dbo.fnMIME64Decode
(
	@Mime VARCHAR(8000)
)
RETURNS VARCHAR(6000)
AS

BEGIN
-- © 2006 Peter Larsson, Developer Workshop, all rights reserved

	DECLARE @Characters VARCHAR(64), @Index SMALLINT,
		@m1 TINYINT, @m2 TINYINT, @m3 SMALLINT, @m4 SMALLINT,
		@p1 TINYINT, @p2 TINYINT, @p3 SMALLINT,
		@PlainText VARCHAR(6000), @Paddings TINYINT

	SELECT	@Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
		@Index = DATALENGTH(@Mime) - 3,
		@Paddings = DATALENGTH(@Mime) - DATALENGTH(REPLACE(@Mime, '=', '')),
		@PlainText = ''

	WHILE @Index > 0
		SELECT	@m1 = CHARINDEX(CAST(SUBSTRING(@Mime, @Index, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
			@m2 = CHARINDEX(CAST(SUBSTRING(@Mime, @Index + 1, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
			@m3 = CHARINDEX(CAST(SUBSTRING(@Mime, @Index + 2, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
			@m4 = CHARINDEX(CAST(SUBSTRING(@Mime, @Index + 3, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
			@p1 = (@m1 & 63) * 4 + (@m2 & 48) / 16,
			@p2 = (@m2 & 15) * 16 + (@m3 & 60) / 4,
			@p3 = (@m3 & 3) * 64 + (@m4 & 63),
			@PlainText = CHAR(@p1) + CHAR(@p2) + CHAR(@p3) + @PlainText,
			@Index = @Index - 4

	RETURN	LEFT(@PlainText, DATALENGTH(@PlainText) - @Paddings)
END


Peter Larsson
Helsingborg, Sweden

Rate

5 (1)

Share

Share

Rate

5 (1)

Related content

SQL 2K SMTP mail on Windows 2K

SP_SQLSMTPMail is an OLE automation implementation of the CDOSYS dll for Windows 2000 which utilizes a network SMTP server rather than an Exchange server/Outlook client. The stored procedure functions similar to xp_sendmail including the ability to run a query and attach the results. No MAPI profile is required. It is also a working, detailed example […]

5 (6)

Clinton Herring

2005-10-12 (first published: 2002-10-08)

3,722 reads