如何将SQL数据库中ChequeImage字段转为URL以在Tableau/PowerBI中可视化

Below are SQL queries for common databases to convert your ChequeImage binary text (hex string) into a Data URI that can be directly visualized in Tableau or PowerBI.

Key Background #

We’ll use the Data URI scheme to embed image data directly into a URL string. This eliminates the need for a separate image host and works natively in both tools. The provided example is a JPEG image (starts with 0xFFD8FFE0), so we use the image/jpeg MIME type.

SQL Server #

Case 1: ChequeImage is stored as VARBINARY #

SELECT
  ChequeID,
  'data:image/jpeg;base64,' + CAST('' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("ChequeImage")))', 'VARCHAR(MAX)') AS ChequeImageURL
FROM YourTable;

Case 2: ChequeImage is stored as a text string (e.g., '0xFFD8FFE0...') #

SELECT
  ChequeID,
  'data:image/jpeg;base64,' + CAST('' AS XML).value('xs:base64Binary(xs:hexBinary(substring(sql:column("ChequeImage"), 3)))', 'VARCHAR(MAX)') AS ChequeImageURL
FROM YourTable;

PostgreSQL #

Case 1: ChequeImage is stored as BYTEA #

SELECT
  cheque_id,
  'data:image/jpeg;base64,' || encode(cheque_image, 'base64') AS cheque_image_url
FROM your_table;

Case 2: ChequeImage is stored as a text string (e.g., '0xFFD8FFE0...') #

SELECT
  cheque_id,
  'data:image/jpeg;base64,' || encode(decode(substring(cheque_image from 3), 'hex'), 'base64') AS cheque_image_url
FROM your_table;

MySQL #

Case 1: ChequeImage is stored as BINARY/VARBINARY #

SELECT
  ChequeID,
  CONCAT('data:image/jpeg;base64,', TO_BASE64(ChequeImage)) AS ChequeImageURL
FROM your_table;

Case 2: ChequeImage is stored as a text string (e.g., '0xFFD8FFE0...') #

SELECT
  ChequeID,
  CONCAT('data:image/jpeg;base64,', TO_BASE64(UNHEX(SUBSTRING(ChequeImage, 3)))) AS ChequeImageURL
FROM your_table;

Using the URL in Tableau/PowerBI #

• Tableau: Drag the ChequeImageURL field to your view, right-click it, and select Mark Type > Image.
• PowerBI: Add the ChequeImageURL column to your dataset. Create an Image visual and map the field to the Image URL property.

内容的提问来源于stack exchange，提问作者Motaz Odeh