How to create a calculated column that encodes in base64

  • 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?

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

  • 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

  • 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!

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply