SQL Base64

This page covers Base64 encoding/decoding functions across different SQL databases including MySQL, SQL Server, PostgreSQL, Oracle, and other popular database systems. Learn how to encode and decode data directly in SQL queries.

Overview

Most modern SQL databases provide built-in Base64 functions for encoding and decoding binary data. This guide covers the specific syntax and functions for each database system, including examples for common use cases like storing images, handling binary data, and data transmission.

MySQL Base64 Functions

-- Encode string to Base64
SELECT TO_BASE64('Hello, World!') AS encoded;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT FROM_BASE64('SGVsbG8sIFdvcmxkIQ==') AS decoded;
-- Result: Hello, World!

-- Encode binary data
SELECT TO_BASE64(BINARY 'Hello') AS binary_encoded;
-- Result: SGVsbG8=

-- Decode to binary
SELECT FROM_BASE64('SGVsbG8=') AS binary_decoded;
-- Result: Hello

-- Store image as Base64
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', TO_BASE64(LOAD_FILE('/path/to/logo.png')));

-- Retrieve and decode image
SELECT name, FROM_BASE64(data_base64) AS image_data 
FROM images WHERE name = 'logo.png';

-- Update existing BLOB to Base64
UPDATE images 
SET data_base64 = TO_BASE64(image_blob) 
WHERE id = 1;

-- Convert Base64 back to BLOB
UPDATE images 
SET image_blob = FROM_BASE64(data_base64) 
WHERE id = 1;

-- URL-safe Base64 encoding (manual implementation)
SELECT REPLACE(REPLACE(REPLACE(TO_BASE64('Hello World'), '+', '-'), '/', '_'), '=', '') 
AS url_safe_base64;
-- Result: SGVsbG8gV29ybGQ

-- URL-safe Base64 decoding (manual implementation)
SELECT FROM_BASE64(
    REPLACE(REPLACE(REPLACE('SGVsbG8gV29ybGQ', '-', '+'), '_', '/'), '', '=' || '')
) AS decoded;
-- Result: Hello World

-- Using in WHERE clause
SELECT * FROM users 
WHERE token = TO_BASE64(CONCAT(user_id, ':', timestamp));

SQL Server Base64 Functions

-- Encode string to Base64 (SQL Server 2016+)
SELECT CAST('Hello, World!' AS VARBINARY(MAX)) FOR XML PATH(''), BINARY BASE64;
-- Alternative method
SELECT CONVERT(VARCHAR(MAX), CAST('Hello, World!' AS VARBINARY(MAX)), 1);

-- Decode Base64 to string
SELECT CAST(CAST('SGVsbG8sIFdvcmxkIQ==' AS XML).value('.', 'VARBINARY(MAX)') AS VARCHAR(MAX));

-- Using FOR XML PATH for encoding
SELECT (SELECT 'Hello, World!' FOR XML PATH(''), BINARY BASE64) AS encoded;

-- Custom Base64 functions (SQL Server 2008+)
CREATE FUNCTION dbo.Base64Encode(@input VARBINARY(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN CAST(@input AS VARCHAR(MAX)) FOR XML PATH(''), BINARY BASE64
END;

-- Store image as Base64
INSERT INTO images (name, data_base64) 
SELECT 'logo.png', 
       CAST(image_data AS VARCHAR(MAX)) FOR XML PATH(''), BINARY BASE64
FROM (SELECT * FROM OPENROWSET(BULK 'C:\logo.png', SINGLE_BLOB) AS x) AS img;

-- Retrieve and decode image
SELECT name, 
       CAST(CAST(data_base64 AS XML).value('.', 'VARBINARY(MAX)') AS VARBINARY(MAX)) AS image_data
FROM images WHERE name = 'logo.png';

-- Update existing VARBINARY to Base64
UPDATE images 
SET data_base64 = CAST(image_data AS VARCHAR(MAX)) FOR XML PATH(''), BINARY BASE64
WHERE id = 1;

-- Create Base64 encoding function
CREATE FUNCTION dbo.Base64Encode(@input VARBINARY(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @result VARCHAR(MAX)
    SET @result = CAST(@input AS VARCHAR(MAX)) FOR XML PATH(''), BINARY BASE64
    RETURN @result
END;

-- Create Base64 decoding function
CREATE FUNCTION dbo.Base64Decode(@input VARCHAR(MAX))
RETURNS VARBINARY(MAX)
AS
BEGIN
    DECLARE @result VARBINARY(MAX)
    SET @result = CAST(CAST(@input AS XML).value('.', 'VARBINARY(MAX)') AS VARBINARY(MAX))
    RETURN @result
END;

-- Usage examples
SELECT dbo.Base64Encode(CAST('Hello' AS VARBINARY(MAX))) AS encoded;
SELECT CAST(dbo.Base64Decode('SGVsbG8=') AS VARCHAR(MAX)) AS decoded;

PostgreSQL Base64 Functions

-- Encode string to Base64
SELECT encode('Hello, World!'::bytea, 'base64') AS encoded;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT convert_from(decode('SGVsbG8sIFdvcmxkIQ==', 'base64'), 'UTF8') AS decoded;
-- Result: Hello, World!

-- Encode binary data
SELECT encode('Hello'::bytea, 'base64') AS binary_encoded;
-- Result: SGVsbG8=

-- Decode to binary
SELECT decode('SGVsbG8=', 'base64') AS binary_decoded;
-- Result: \x48656c6c6f

-- Store image as Base64
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', encode(lo_get(lo_import('/path/to/logo.png')), 'base64'));

-- Retrieve and decode image
SELECT name, decode(data_base64, 'base64') AS image_data 
FROM images WHERE name = 'logo.png';

-- Update existing BYTEA to Base64
UPDATE images 
SET data_base64 = encode(image_data, 'base64') 
WHERE id = 1;

-- Convert Base64 back to BYTEA
UPDATE images 
SET image_data = decode(data_base64, 'base64') 
WHERE id = 1;

-- URL-safe Base64 encoding
SELECT replace(replace(replace(encode('Hello World'::bytea, 'base64'), '+', '-'), '/', '_'), '=', '') 
AS url_safe_base64;
-- Result: SGVsbG8gV29ybGQ

-- URL-safe Base64 decoding
SELECT convert_from(decode(
    replace(replace(replace('SGVsbG8gV29ybGQ', '-', '+'), '_', '/'), '', '=' || ''),
    'base64'
), 'UTF8') AS decoded;
-- Result: Hello World

-- Using in WHERE clause
SELECT * FROM users 
WHERE token = encode(concat(user_id, ':', timestamp)::bytea, 'base64');

Oracle Base64 Functions

-- Encode string to Base64
SELECT UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('Hello, World!')) AS encoded FROM DUAL;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE('SGVsbG8sIFdvcmxkIQ==')) AS decoded FROM DUAL;
-- Result: Hello, World!

-- Encode binary data
SELECT UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('Hello')) AS binary_encoded FROM DUAL;
-- Result: SGVsbG8=

-- Decode to binary
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE('SGVsbG8=')) AS binary_decoded FROM DUAL;
-- Result: Hello

-- Store image as Base64
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', UTL_ENCODE.BASE64_ENCODE(image_blob));

-- Retrieve and decode image
SELECT name, UTL_ENCODE.BASE64_DECODE(data_base64) AS image_data 
FROM images WHERE name = 'logo.png';

-- Update existing BLOB to Base64
UPDATE images 
SET data_base64 = UTL_ENCODE.BASE64_ENCODE(image_blob) 
WHERE id = 1;

-- Convert Base64 back to BLOB
UPDATE images 
SET image_blob = UTL_ENCODE.BASE64_DECODE(data_base64) 
WHERE id = 1;

-- Create custom Base64 functions
CREATE OR REPLACE FUNCTION base64_encode(p_input VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
    RETURN UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(p_input));
END;

CREATE OR REPLACE FUNCTION base64_decode(p_input VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
    RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(p_input));
END;

-- Usage examples
SELECT base64_encode('Hello World') AS encoded FROM DUAL;
SELECT base64_decode('SGVsbG8gV29ybGQ=') AS decoded FROM DUAL;

Other Database Systems

-- SQLite doesn't have built-in Base64 functions
-- You need to use extensions or custom implementations

-- Using base64 extension (if available)
SELECT base64('Hello, World!') AS encoded;
SELECT base64_decode('SGVsbG8sIFdvcmxkIQ==') AS decoded;

-- Manual Base64 implementation (simplified)
-- This is a basic example - full implementation would be more complex
CREATE TABLE base64_chars (
    val INTEGER PRIMARY KEY,
    char TEXT
);

-- Insert Base64 alphabet
INSERT INTO base64_chars VALUES 
(0,'A'),(1,'B'),(2,'C'),(3,'D'),(4,'E'),(5,'F'),(6,'G'),(7,'H'),
(8,'I'),(9,'J'),(10,'K'),(11,'L'),(12,'M'),(13,'N'),(14,'O'),(15,'P'),
(16,'Q'),(17,'R'),(18,'S'),(19,'T'),(20,'U'),(21,'V'),(22,'W'),(23,'X'),
(24,'Y'),(25,'Z'),(26,'a'),(27,'b'),(28,'c'),(29,'d'),(30,'e'),(31,'f'),
(32,'g'),(33,'h'),(34,'i'),(35,'j'),(36,'k'),(37,'l'),(38,'m'),(39,'n'),
(40,'o'),(41,'p'),(42,'q'),(43,'r'),(44,'s'),(45,'t'),(46,'u'),(47,'v'),
(48,'w'),(49,'x'),(50,'y'),(51,'z'),(52,'0'),(53,'1'),(54,'2'),(55,'3'),
(56,'4'),(57,'5'),(58,'6'),(59,'7'),(60,'8'),(61,'9'),(62,'+'),(63,'/');

-- MariaDB has the same Base64 functions as MySQL
-- TO_BASE64() and FROM_BASE64() functions

-- Encode string to Base64
SELECT TO_BASE64('Hello, World!') AS encoded;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT FROM_BASE64('SGVsbG8sIFdvcmxkIQ==') AS decoded;
-- Result: Hello, World!

-- Working with BLOB data
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', TO_BASE64(LOAD_FILE('/path/to/logo.png')));

-- Retrieve and decode
SELECT name, FROM_BASE64(data_base64) AS image_data 
FROM images WHERE name = 'logo.png';

-- DB2 Base64 functions
-- Encode string to Base64
SELECT BASE64('Hello, World!') AS encoded FROM SYSIBM.SYSDUMMY1;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT BASE64_DECODE('SGVsbG8sIFdvcmxkIQ==') AS decoded FROM SYSIBM.SYSDUMMY1;
-- Result: Hello, World!

-- Working with BLOB data
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', BASE64(image_blob));

-- Retrieve and decode
SELECT name, BASE64_DECODE(data_base64) AS image_data 
FROM images WHERE name = 'logo.png';

Best Practices

• Performance: Base64 encoding increases data size by ~33%, consider the impact on storage and network.
• Indexing: Base64 encoded data is not suitable for indexing or searching - store original data separately.
• Security: Base64 is not encryption - use proper encryption for sensitive data.
• Data Types: Use appropriate data types (VARCHAR, TEXT, CLOB) for Base64 strings.
• Error Handling: Always validate Base64 input before decoding to avoid errors.
• URL Safety: Use URL-safe Base64 variants when embedding in URLs or JSON.
• Large Data: For large binary data, consider storing files separately and only storing references.