6

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
Viacheslav Nefedov
  • 2,259
  • 3
  • 15
  • 15
  • change "cast(cast(@input as varbinary(max)) as varchar(max))" to "cast(cast(@input as varchar(max)) as varbinary(max))" – Richard Boyce Mar 03 '16 at 13:02
  • still having 'null' as a result. The question was that when 123 is converted to a strings it takes three chars to represent the result because of it's 10 based. And when converting this to a base64 it should take only two characters. So it should be converted this way: bigint->binary->base64 string – Viacheslav Nefedov Mar 03 '16 at 13:17

2 Answers2

2

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

Richard Boyce
  • 413
  • 5
  • 12
1

Not wanting to leave an answer that doesn't answer the question, here's how to remove a number of leading A's from a string (but the answer @Richard gave is better):

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

----------------
12345ABCD9876==
Liesel
  • 2,929
  • 2
  • 12
  • 18
  • Thanks for the answer, but using recursive operation seems to be time consuming here. I'm trying to find a way to avoid adding these 'A's in the beginning of the base64 string or at least to delete them without recursion – Viacheslav Nefedov Mar 03 '16 at 11:16
  • Actually 'A' in base64 means zero, 'B' - 1 and so on. So base64 'ABA' means 64 and 'BA' - too. See https://en.wikipedia.org/wiki/Base64 – Viacheslav Nefedov Mar 03 '16 at 11:25
  • This code seems to be a bit easier: select replace(ltrim(replace(@val,'A',' ')),' ','A') – Viacheslav Nefedov Mar 04 '16 at 12:02