IF OBJECT_ID (N'dbo. F_base64_decode ') IS NOT NULL
DROP FUNCTION dbo.f_base64_decode;
GO
/ *-- == Base64 decoding =================================
In SQL Server, use the FOR XML generated instance xml, binary data
Use base64 encoding, and interpretation of xml, they will have no corresponding decoding method.
Use this function to solve this problem
Base64 encoding the 3-bit bytes (* 8 = 24) into four 6-bit bytes (* 6 = 24)
After meeting in front of the reign of the two, forming spaces in the form of a byte. 64 characters with the following re-
Said: "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 + /"
If the input data length is not a multiple of 3, then the result of inadequate coding part with an equal sign "="
Complement.
-------------------------------------------------- -------
- Call Example
SELECT - reduced to a string
val = CONVERT (nvarchar (max), val)
FROM (
SELECT - decode base64 encoded
val = dbo.f_base64_decode (val)
FROM (- base64 encoded by for xml generation
SELECT val = (SELECT CONVERT (varbinary (max), N'base64 test ') FOR XML PATH (''), TYPE). Value ('/',' nvarchar (max) ') UNION ALL
SELECT val = (SELECT CONVERT (varbinary (max), N'base64 decoding test ') FOR XML PATH (''), TYPE). Value ('/',' nvarchar (max) ')
) DATA
) A
-------------------------------------------------- -------
- Environmental requirements
Apply to sql server 2005 or later
- ==== Zou Jian-.02 (reference please keep this information) =============== backup / bin / conf / data / log / maint / svn / tmp /
CREATE FUNCTION dbo.f_base64_decode (
@ Input varchar (max)
) RETURNS varbinary (max)
AS
BEGIN
DECLARE
@ Base64 char (64),
@ Pos int,
@ Len int,
@ Output varbinary (max);
SELECT
@ Base64 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 + /',
@ Pos = 1,
@ Len = LEN (@ input),
@ Output = 0x;
IF @ len% 4> 0
RETURN NULL;
WHILE @ pos <@ len
BEGIN
SELECT
@ Output = @ output
+ CONVERT (binary (1), ((v1 & 63) backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 4) | ((v2 & 48) / 16))
+ CONVERT (binary (1), ((v2 & 15) backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 16) | ((v3 & 60) / 4))
+ CONVERT (binary (1), ((v3 & 3) backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 64) | ((v4 & 63) / 1)),
@ Pos = @ pos + 4
FROM (
SELECT
v1 = CHARINDEX (SUBSTRING (@ input, @ pos + 0, 1) COLLATE Chinese_PRC_BIN, @ base64) - 1,
v2 = CHARINDEX (SUBSTRING (@ input, @ pos + 1, 1) COLLATE Chinese_PRC_BIN, @ base64) - 1,
v3 = CHARINDEX (SUBSTRING (@ input, @ pos + 2, 1) COLLATE Chinese_PRC_BIN, @ base64) - 1,
v4 = CHARINDEX (SUBSTRING (@ input, @ pos + 3, 1) COLLATE Chinese_PRC_BIN, @ base64) - 1
) A;
END;
RETURN (SUBSTRING (@ output, 1, @ len / 4 backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 3 - 3 + CHARINDEX ('=', RIGHT (@ input, 2) + ' =')));
END;
GO