SQL Server base64编码存储函数

Question

我需要一个简单的存储函数，用于进一步的SQL Server base64编码，但是对于我传递的每个字符串都返回null。我认为sql:parameter无法与输入函数参数一起使用，但不知道如何避免它。

这是我的代码：

ALTER FUNCTION [dbo].[usf_base64_encode]
    (@value nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    RETURN cast('' as xml).value('xs:base64Binary(sql:variable("@value"))', 'varchar(max)')
END

这段代码片段能够正常运行，但是它声明了局部变量，而在函数中是不可能做到的。

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)
set @source = convert(varbinary(max), 'Hello Base64')
set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)')

select
    convert(varchar(max), @source) as source_varchar,
    @source as source_binary,
    @encoded as encoded,
    @decoded as decoded_binary,
    convert(varchar(max), @decoded) as decoded_varchar

- Alex Zhukovskiy

Answer 1

我肯定是相当愚蠢的。这段代码可以正常工作：

ALTER FUNCTION [dbo].[usf_base64_encode]
(
    @value varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @source varbinary(max) = convert(varbinary(max), @value)
    RETURN cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
END

- Alex Zhukovskiy

Answer 2

这是个好的例子。

/****** Object:  UserDefinedFunction [dbo].[jtfnChangeStringBase]    Script Date: 05/18/2011 11:39:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Mark Gascoyne
-- Create date: 18 MAY 2011
-- Description: Converts any base-n string to any base-n string
-- =============================================
ALTER FUNCTION [MG].[ChangeStringBase]
(
    @inputEncode VARCHAR(60), -- the input string of base inputBase
    @inputBase BIGINT,  -- the input base of inputString
    @base BIGINT -- the output base
)
RETURNS VARCHAR(60)
AS
BEGIN
    declare @map VARCHAR(60)
    set @map = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwx'

    if (@inputBase>len(@map))
        RETURN '@inputBase is greater than the decode map'

    if (@base>len(@map))
        RETURN '@base is greater than the encode map'

    --declare @inputEncode VARCHAR(50)
    --set @inputEncode = 'FF'

    --declare @inputBase int
    --set @inputBase=16

    declare @num BIGINT -- base-10 representation of @inputString
    --set @num = 255

    --declare @base int
    --set @base = 60

    declare @encode varchar(60) --@base encoded string

    --decode @inputString-@inputBase to base-10
    ;
    with 
    tokens(i, j) -- get base-10 value for base-n string
    as(
        select 1 as i
             ,CONVERT(BIGINT,(CHARINDEX(SUBSTRING(@inputEncode,len(@inputEncode),1),
                            SUBSTRING(@map,1,@inputBase) 
                            COLLATE Latin1_General_CS_AS))-1)
        union all
        select i+1 as i
             ,power(@inputBase,i)*((CHARINDEX(SUBSTRING(@inputEncode,len(@inputEncode)-(i),1),
                            SUBSTRING(@map,1,@inputBase) 
                            COLLATE Latin1_General_CS_AS))-1)

        from tokens
        where i<len(@inputEncode)
    )
    select @num=sum(j) from tokens
    ;
    --encode @num base-10 int to @base
    with 
    tokens(i,j,k, map) -- get base-n value of base-10 string @num
    as(
        select 1 as i
             ,@num % @base as j
             ,@num / @base as k
             ,SUBSTRING(@map, (@num % @base)+1,1)
        union all
        select i+1 as i
             ,k % @base as j
             ,k / @base as k
              ,SUBSTRING(@map, (k % @base)+1,1)
        from tokens
        where k>0
    )

    select @encode=isnull(@encode,'')+map
    from tokens
    order by i desc

    RETURN @encode  

END

- Mark

---

没问题，但是我更喜欢稳定的内置解决方案而不是自定义代码。 - Alex Zhukovskiy

不用担心，这段代码已经在生产环境中使用了至少4年，甚至更长时间。 - Mark