SQL - Text & Image Functions

SQL - Working with String and LOB Data

This chapter covers modern techniques for handling text and Large Object (LOB) data in SQL. We’ll explore standard string manipulation functions and discuss best practices for storing large data like documents or images.

Note: The functions TEXTPTR() and TEXTVALID() are deprecated features from old versions of SQL Server and should not be used in modern development. This chapter replaces that outdated content with current, standard-compliant practices.

Modern Data Types for Text and Binary Data

Choosing the right data type is crucial for performance and storage efficiency.

• VARCHAR(n): The standard for variable-length strings up to a specified maximum length n. Use this for most text fields like names, titles, and short descriptions.
• CHAR(n): For fixed-length strings. Only use this if you are certain all values will be the exact same length, such as two-letter country codes (‘US’, ‘CA’, ‘DE’). Otherwise, VARCHAR is more efficient.
• TEXT / CLOB / VARCHAR(MAX): For long-form character data of variable, potentially very large length, such as blog posts or articles. The exact name varies by SQL dialect (TEXT in MySQL/PostgreSQL, VARCHAR(MAX) in SQL Server, CLOB in Oracle).
• VARBINARY(n) / BYTEA / BLOB: For storing variable-length binary data (Large Objects, or LOBs). This includes images, PDFs, or other files. Names vary by dialect (BLOB in MySQL/Oracle, BYTEA in PostgreSQL, VARBINARY(MAX) in SQL Server).

Common SQL String Functions

Most SQL databases support a standard set of functions for manipulating string data. Let’s demonstrate with a products table.

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  sku VARCHAR(20)
);

INSERT INTO products (id, name, sku) VALUES
(1, '  Modern SQL Guide  ', 'SQL-MOD-001'),
(2, 'learning python', 'PY-LRN-002');

Function	Example	Description & Result
CONCAT()	SELECT CONCAT(name, ' (', sku, ')') FROM products WHERE id = 1;	Joins strings together. Result: Modern SQL Guide (SQL-MOD-001)
LENGTH() / LEN()	SELECT LENGTH(sku) FROM products WHERE id = 1;	Returns the number of characters. Result: 11
UPPER() / LOWER()	SELECT UPPER(name) FROM products WHERE id = 2;	Converts case. Result: LEARNING PYTHON
TRIM()	SELECT TRIM(name) FROM products WHERE id = 1;	Removes leading and trailing spaces. Result: Modern SQL Guide
SUBSTRING()	SELECT SUBSTRING(sku FROM 1 FOR 3) FROM products WHERE id = 1;	Extracts a substring. Result: SQL
REPLACE()	SELECT REPLACE(name, 'python', 'Python 3') FROM products WHERE id = 2;	Replaces occurrences of a substring. Result: learning Python 3
POSITION()	SELECT POSITION('LRN' IN sku) FROM products WHERE id = 2;	Finds the starting position of a substring. Result: 4

Best Practices for Storing Large Objects (Images, Files)

While you can store binary data like images directly in the database using BLOB or VARBINARY(MAX) columns, it is often not the recommended approach for modern applications. Storing files in the database can lead to:

• Increased Database Size: Rapidly bloats the database, making backups and restores slow and expensive.
• Performance Issues: Database servers are optimized for structured data retrieval, not for serving large binary files.
• Application Complexity: Requires extra application logic to handle streaming data to and from the database.

The Modern Approach: Object Storage

The industry-standard best practice is to store the files in a dedicated object storage service and store only a reference (a URL or a unique key) to the file in your database.

Workflow:

1. The application uploads the user’s file (e.g., a profile picture) directly to an object storage service like Amazon S3, Google Cloud Storage, or Azure Blob Storage.
2. The storage service returns a unique URL or identifier for the stored file.
3. The application saves this URL/identifier in a VARCHAR column in the database, alongside the other user data.

Example Schema

-- Storing a reference to an image, not the image itself
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    bio TEXT,
    profile_picture_url VARCHAR(255)  -- Stores the URL to the image on S3, etc.
);

This approach keeps your database lean and fast, while leveraging specialized services for what they do best.