November 12, 2018 at 9:03 am
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?
November 12, 2018 at 9:57 am
Xml data type methods are not supported in computed column definitions.
Create a scalar user-defined function to wrap the method invocation.
November 12, 2018 at 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
November 13, 2018 at 3:03 am
Jonathan AC Roberts - Monday, November 12, 2018 10:10 AMCREATE 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]) XRETURN @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!
November 13, 2018 at 7:18 am
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