sql server base64解密函数

最新推荐文章于 2025-02-25 14:57:33 发布

ankg83749

最新推荐文章于 2025-02-25 14:57:33 发布

阅读量655

点赞数

文章标签：数据库 c#

原文链接：http://www.cnblogs.com/reese-blogs/p/5531266.html

版权

本文介绍了一个用于SQL Server的自定义函数base64_decode，该函数能够将Base64编码的字符串转换为原始的二进制数据。文章详细展示了函数的创建过程，包括参数设置、返回类型及内部实现逻辑。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [LVP]
GO
/****** Object:  UserDefinedFunction [dbo].[base64_decode]    Script Date: 2016/5/26 10:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[base64_decode]  
(  
@encoded_text varchar(max)  
)  
RETURNS varbinary(max)  
AS  
BEGIN  
DECLARE  
    @output varbinary(max),  
    @block_start int,  
    @encoded_length int,  
    @decoded_length int,  
    @mapr binary(122)  
IF LEN(@encoded_text) & 3 > 0  
    OR @encoded_text LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=]%' COLLATE Latin1_General_Bin  
    RETURN NULL  
SET @output = 0x  
-- The nth byte of @mapr contains the base64 value of the character with an ASCII value of n.  
-- eg. 65th byte = 0x00 = 0 = value of 'A'  
SET @mapr =  
          0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF -- 1-33  
        + 0xFFFFFFFFFFFFFFFFFFFF3EFFFFFF3F3435363738393A3B3C3DFFFFFF00FFFFFF -- 33-64  
        + 0x000102030405060708090A0B0C0D0E0F10111213141516171819FFFFFFFFFFFF -- 65-96  
        + 0x1A1B1C1D1E1F202122232425262728292A2B2C2D2E2F30313233 -- 97-122  
--get the number of blocks to be decoded  
SET @encoded_length = LEN(@encoded_text)  
SET @decoded_length = @encoded_length / 4 * 3  
--for each block  
SET @block_start = 1  
WHILE @block_start < @encoded_length  
BEGIN  
    --decode the block and add to output  
    --BINARY values between 1 and 4 bytes can be implicitly cast to INT  
    SET @output = @output +  
        CAST(CAST(  
              SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start    , 1)), 1) * 262144  
            + SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 1, 1)), 1) * 4096  
            + SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 2, 1)), 1) * 64  
            + SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 3, 1)), 1)  
        AS int) AS binary(3))  
    SET @block_start = @block_start + 4  
END  
IF RIGHT(@encoded_text, 2) = '=='  
    SET @decoded_length = @decoded_length - 2  
ELSE IF RIGHT(@encoded_text, 1) = '='  
    SET @decoded_length = @decoded_length - 1  
RETURN SUBSTRING(@output, 1, @decoded_length)  
END

转载于:https://www.cnblogs.com/reese-blogs/p/5531266.html