Store RTF in SQL Server VARCHAR(MAX)

by

Posted on by

RTF(Rich Text Format) can be stored as SQL Server VARCHAR(MAX) data.

1. SQL Server Character Large Field Overview

SQL Server’s VARCHAR(MAX) replaces the deprecated TEXT type, supporting up to 2GB for large character data.

Compatible with regular VARCHAR syntax, no special initialization required, simple to operate.

2. Pure T-SQL Implementation Steps

Step 1: Create Table with VARCHAR(MAX) Field

CREATE TABLE RTF_DOCS (
    DOC_ID INT PRIMARY KEY,
    DOC_NAME VARCHAR(100) NOT NULL,
    RTF_CONTENT VARCHAR(MAX) NOT NULL, -- Core field for RTF storage
    CREATE_TIME DATETIME DEFAULT GETDATE()
);

Step 2: Insert RTF Text into VARCHAR(MAX)

-- Method 1: Insert short RTF text directly
INSERT INTO RTF_DOCS (DOC_ID, DOC_NAME, RTF_CONTENT)
VALUES (
    1,
    'test.rtf',
    '{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Calibri;}} \\pard\\f0\\fs22 SQL Server RTF Test\\par}'
);

-- Method 2: Insert external RTF file (read via OPENROWSET)
INSERT INTO RTF_DOCS (DOC_ID, DOC_NAME, RTF_CONTENT)
SELECT 
    2,
    'imported.rtf',
    BulkColumn -- Read file content as character stream
FROM OPENROWSET(
    BULK 'C:\path\to\test.rtf', -- Local RTF file path
    SINGLE_CLOB -- Read as character (SINGLE_BLOB = binary)
) AS RTF_FILE;

Step 3: Query/Edit RTF Content

-- Query full RTF content
SELECT DOC_NAME, RTF_CONTENT FROM RTF_DOCS WHERE DOC_ID = 1;

-- Truncate RTF preview (first 150 characters)
SELECT DOC_NAME, SUBSTRING(RTF_CONTENT, 1, 150) AS RTF_PREVIEW FROM RTF_DOCS;

-- Update RTF content
UPDATE RTF_DOCS 
SET RTF_CONTENT = '{\\rtf1\\ansi\\deff0 \\pard\\f0\\fs28 Updated SQL Server RTF\\par}'
WHERE DOC_ID = 1;

Step 4: Delete RTF Record

DELETE FROM RTF_DOCS WHERE DOC_ID = 2;

3. Simplify SQL Server VARCHAR(MAX) RTF Management with DBBlobEditor

DBBlobEditor makes RTF management for SQL Server VARCHAR(MAX) easier:

4. Notes

  • Enable OPENROWSET first: `sp_configure ‘show advanced options’, 1; RECONFIGURE; sp_configure ‘ad hoc distributed queries’, 1; RECONFIGURE;`
  • VARCHAR(MAX) is character encoding-sensitive; store RTF in UTF-8 uniformly
  • Batch concatenation is recommended for large RTF inserts to avoid long character strings in single commits

Related Guides