Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save sarangbk/af3790a9ec50fe4ad7cc30b20b6a8a9a to your computer and use it in GitHub Desktop.
Save sarangbk/af3790a9ec50fe4ad7cc30b20b6a8a9a 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
@maksimpv29
Copy link

а с русским языком как быть?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment