Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save morriekken/c3a4deda42241c318dd5d52b590cbc61 to your computer and use it in GitHub Desktop.
Save morriekken/c3a4deda42241c318dd5d52b590cbc61 to your computer and use it in GitHub Desktop.
MSSQL Convert a VARCHAR to Base64 encoding and vice-versa
-- VARCHAR TO Base64
IF OBJECT_ID (N'[dbo].[uFnStringToBase64]', N'FN') IS NOT NULL
DROP FUNCTION uFnStringToBase64;
GO
CREATE FUNCTION [dbo].[uFnStringToBase64]
(
@InputString VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT
CAST(N'' AS XML).value(
'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
, 'VARCHAR(MAX)'
)
FROM (
SELECT CAST(@InputString AS VARBINARY(MAX)) AS bin
) AS RetVal
)
END;
GO
-- Base64 To VARCHAR
IF OBJECT_ID (N'[dbo].[uFnBase64ToString]', N'FN') IS NOT NULL
DROP FUNCTION uFnBase64ToString;
GO
CREATE FUNCTION [dbo].[uFnBase64ToString]
(
@Input_Base64_Value VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT
CAST(
CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Input_Base64_Value"))', 'VARBINARY(MAX)')
AS VARCHAR(MAX)
) AS RetVal
)
END
GO
-- Usage
SELECT [dbo].[uFnStringToBase64]('Encode Me') AS EncodedString
SELECT [dbo].[uFnBase64ToString]([dbo].[uFnStringToBase64]('Encode Me')) AS DecodedString
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment