Geek Answers Handbook

Saved SQL Server Database Encoding Function

I need a simple stored function to further encode the SQL Server database, but it returns null for every row I pass. I think sql: parameter does not work with input function parameter, but does not know how to avoid it.

This is my code:

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

This piece of code works, but it declares local variables that cannot be executed in the function.

 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
+1
sql-server tsql encoding base64
source share
2 answers

OK, I'm totally ridiculous. This code works:

 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
+2
source share

This is a good option.

 /****** 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 @inputSt ring-@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
0
source share

More articles:


All Articles