SQL Server: how to remove leading 'A's from base64 string

Question

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

Sample code:

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 just ew because of the idea is to make the final string as short as it possible and base64 string should be shorter than base10.

update 1: as were suggested by Richard Boyce I tried to convert bigint to the 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 a base64binary string and to remove leading 'A's and trailing '='s. It's not perfect, 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

Rather than trying to remove leading "A's" from the encoded result look at preventing them in the first place.

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

Try this

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))

Note that when encoded "123" becomes "MTIz" not ew==