SQL Server: how to remove leading row A from base64

Question

I am working on SQL Server and trying to create a single key combining data from bigint and string columns. To minimize bigint's size, represented as strings, I use Base 64 encoding. The problem is that the result includes listing “A meaning base64 zeroes and increasing the size of the resulting field. How to remove these leading A using T-SQL or XQuery ?

Code example:

 DECLARE @binInput VARBINARY(MAX) SET @binInput = CAST(123 AS VARBINARY(MAX)) SELECT CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@binInput"))', 'varchar(max)') 

I have a resulting AAAAew== , where I would prefer to see only ew , because the idea is to make the final line as short as possible, and the base64 string should be shorter than base10.

update 1 : as suggested by Richard Boyce, I tried converting bigint to an equal string, but it gives null as a result of base64 conversion

 declare @input bigint declare @varInput nvarchar(max) set @input = 123 set @varInput = cast(cast(@input as varbinary(max)) as varchar(max)) select CAST(N'' AS xml).value('xs:base64Binary(sql:variable("@varInput"))', 'varchar(max)') 

update 2 : the current solution is to get the base64binary string and remove the leading "A and trailing" =. This is not ideal, so any suggestions are welcome. Actual code:

 declare @input bigint set @input = 1234567890 declare @output varchar(max) set @output = (select cast(@input as varbinary(max)) for xml path(''),binary base64) set @output = replace(ltrim(replace(@output,'A',' ')),' ','A') -- remove leading 'A's set @output = replace(@output,'=','') -- remove trailing '='s select @output 

Answer 1

Instead of trying to remove the leading “A” from the encoded result, look at preventing them first.

You need to convert your number to a string before encoding.

try it

 DECLARE @binInput VARBINARY(MAX) SET @binInput = CAST(CAST(123 AS VARCHAR(MAX)) AS VARBINARY(MAX)) DECLARE @Result VARCHAR(MAX) SELECT @Result = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@binInput"))', 'varchar(max)') SELECT CAST(CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Result"))', 'varbinary(max)') AS VARCHAR(MAX)) 

Please note that when encoding "123" becomes "MTIz" not ew ==

Answer 2

Not wanting to leave an answer that does not answer the question, here's how to remove several leading A from the line (but it's better to answer @Richard):

 DECLARE @VAL NVARCHAR(MAX) = N'AAAA12345ABCD9876==' SELECT SUBSTRING(@VAL,PATINDEX('%[^A]%',@VAL), LEN(@VAL)) ---------------- 12345ABCD9876==