How to create a calculated column that encodes in base64

• Rob

  Ten Centuries

  Points: 1035

  More actions

  November 12, 2018 at 9:03 am

  #380812

  I am trying to create a calculated column which is a single other column but encoded in base64. I have seen select statements that use FOR XML to return a base64 encoded string, however I get a syntax error when I try to use it in the formula for a computed column.

  For example is I insert a string "abc" in to Column1, I want Column 2 to be a calculated (persisted)  field that shows "abc" encoded in base64, so it should show "YWJj"

  I can do simple formulas like Column1+'blah' to append a string to it, but if I try and use something like SELECT Column1 FOR XML AUTO then I get the error "Error validating the formula for column 'Column2'.

  Is what I am trying to achieve beyond the capabilities of the computed column formula?

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  November 12, 2018 at 9:57 am

  #2013237

  Xml data type methods are not supported in computed column definitions.
  Create a scalar user-defined function to wrap the method invocation.

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  November 12, 2018 at 10:10 am

  #2013241

  CREATE FUNCTION ConvertToBase64
(
    @P1 varchar(100)
)
RETURNS nvarchar(100)
AS
BEGIN
    DECLARE @ResultVar nvarchar(MAX)

      SELECT @ResultVar=CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("P1")))', 'VARCHAR(MAX)')
  FROM (SELECT CONVERT(VARBINARY(MAX),@P1) [P1]) X

      RETURN @ResultVar

  END
GO

  GO
DROP TABLE TestBase64
GO
CREATE TABLE TestBase64 (SomeText varchar(100))
GO
ALTER TABLE TestBase64
ADD myCalcColumn AS (dbo.ConvertToBase64(SomeText))
GO
INSERT INTO TestBase64 VALUES ('abc')
GO
SELECT * FROM TestBase64

• Rob

  Ten Centuries

  Points: 1035

  More actions

  November 13, 2018 at 3:03 am

  #2013297

  Jonathan AC Roberts - Monday, November 12, 2018 10:10 AM

  CREATE FUNCTION ConvertToBase64
  (
      @P1 varchar(100)
  )
  RETURNS nvarchar(100)
  AS
  BEGIN
      DECLARE @ResultVar nvarchar(MAX)

      SELECT @ResultVar=CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("P1")))', 'VARCHAR(MAX)')
    FROM (SELECT CONVERT(VARBINARY(MAX),@P1) [P1]) X

      RETURN @ResultVar

  END
GO

  GO
DROP TABLE TestBase64
GO
CREATE TABLE TestBase64 (SomeText varchar(100))
GO
ALTER TABLE TestBase64
ADD myCalcColumn AS (dbo.ConvertToBase64(SomeText))
GO
INSERT INTO TestBase64 VALUES ('abc')
GO
SELECT * FROM TestBase64

  Just implemented this and it works great! Thanks Jonathan - I did not know it was possible to put scalar-functions in to calculated columns!

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  November 13, 2018 at 7:18 am

  #2013316

  If you want to improve performance on selects (not updates and inserts) you can create the function with schemabinding, then you can persist the column:
  ALTER FUNCTION ConvertToBase64
(
  @P1 varchar(100)
)
RETURNS nvarchar(100)
WITH SCHEMABINDING
...

  ALTER TABLE TestBase64
ADD myCalcColumn AS (dbo.ConvertToBase64(SomeText)) PERSISTED
  You can even add an index to it after that.