Skip to main content

Converting SQL Server Binary Image Data to HTML Image URL

 


When working with images stored as binary data in SQL Server, you might need to display them in a web application. A convenient way to do this is by converting the binary data into a Base64-encoded string and using it as a data URL in HTML. Best suited for small thumbnail photos or icons.

Storing Images in SQL Server

Images are often stored in SQL Server as VARBINARY(MAX) columns. For example, a table structure for storing user photos might look like this:

CREATE TABLE UserPhotos (
    UserID INT PRIMARY KEY,
    PhotoThumbnail VARBINARY(MAX)
);

Converting the Binary Data to a Base64 Image URL

SQL Server doesn’t provide built-in functions to encode binary data as Base64, but we can achieve this using FOR XML PATH combined with BINARY BASE64. Below is a query to transform the binary image data into a Base64 string formatted as a data URL:

SELECT
    CASE
        WHEN p.PhotoThumbnail IS NOT NULL
        THEN 'data:image/png;base64,' +
            REPLACE(
                REPLACE(
                    (SELECT p.PhotoThumbnail FOR XML PATH(''), BINARY BASE64),
                    '<PhotoThumbnail>', ''
                ),
                '</PhotoThumbnail>', ''
            )
        ELSE NULL
    END AS PhotoURL
FROM UserPhotos p;

Explanation:

  • FOR XML PATH('') is used to convert the binary data to a Base64 string.

  • BINARY BASE64 ensures the binary data is properly encoded.

  • REPLACE(..., '<PhotoThumbnail>', '') removes the XML tags from the output.

  • The result is prefixed with data:image/png;base64, to create a valid data URL.

Using the Base64 String in HTML

Once you have the Base64-encoded image, you can use it directly in an <img> tag:

<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAA..." alt="User Photo">

Replace the src value with the actual Base64 string returned from the SQL query.

Considerations

  • Performance: Storing and retrieving large binary data in SQL Server can impact performance. Consider using external storage (e.g., Azure Blob Storage, AWS S3) and storing image URLs in the database.

  • Size Limitation: Base64 encoding increases the size of the image data by approximately 33%.

  • Compatibility: Some browsers may limit the size of data URLs that can be used in src attributes.

By using this approach, you can dynamically retrieve and display images stored in SQL Server without needing to save them as separate files on a web server.

Comments

Post a Comment

Popular posts from this blog

Removing HTML Tags from Text Using SQL Server User-Defined Function

  Introduction: In this blog post, we'll explore how to create and use a SQL Server User-Defined Function (UDF) to remove HTML tags from a text string. This function can be handy when you need to extract plain text from HTML content stored in your database. Creating the Function: First, let's create the SQL Server UDF named udf_StripHTML . This function takes a VARCHAR(MAX) parameter @HTMLText , which represents the HTML content from which we want to remove the tags. It returns a VARCHAR(MAX) value, representing the text stripped of HTML tags. sql SET QUOTED_IDENTIFIER ON GO  CREATE FUNCTION [dbo].[udf_StripHTML] ( @HTMLText VARCHAR (MAX)) RETURNS VARCHAR (MAX) AS BEGIN DECLARE @Start INT   DECLARE @End INT   DECLARE @Length INT   SET @Start = CHARINDEX( '<' , @HTMLText )  SET @End = CHARINDEX( '>' , @HTMLText , CHARINDEX( '<' , @HTMLText ))  SET @Length = ( @End - @Start ) + 1   WHILE @Start > 0 AND @...
Post a Comment
Read more

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF.   The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008   ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.        Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.        Give this account browse access on the reporting server for the desired reports. 3.        To get this working in visual studio 2010 (I am using t...
1 comment
Read more

How to Automatically Create SQL Server Views from MySQL Tables Using OPENQUERY (An alternative to ETL)

If you have a linked server from SQL Server to MySQL, you can automate importing data and creating views using dynamic SQL. This is useful when integrating external MySQL data into a Microsoft SQL Server reporting or analytics environment. 🔗 Setup: Linked Server to MySQL Make sure you have already set up your MySQL linked server in SQL Server (for example, named SB ), and that you can run queries like the following: SELECT * FROM OPENQUERY(SB, 'SELECT * FROM your_table'); ⚙️ Goal We want to dynamically create SQL Server views for all base tables in a MySQL database, using a format like: CREATE VIEW [dbo].[lnk_table_name] AS SELECT * FROM OPENQUERY(SB, 'SELECT * FROM table_name WHERE deleted_at IS NULL'); But not all MySQL tables have a deleted_at column. So, we will check whether the column exists before appending the WHERE clause. 🧠 Full SQL Script This SQL Server script loops through all MySQL tables and generates the appropriate view creation stat...
Post a Comment
Read more