SQL - STUFF() / INSERT() | Application Architect

Key Insights

• SQL Server’s STUFF() and MySQL’s INSERT() perform similar string manipulation by replacing portions of text at specified positions, but with different syntax and parameter ordering • Both functions excel at dynamic string modifications like masking sensitive data, formatting identifiers, and manipulating delimited lists without complex substring concatenation • Understanding zero-based vs one-based indexing differences between these functions prevents off-by-one errors when porting queries across database platforms

Understanding STUFF() in SQL Server

STUFF() replaces a portion of a string with another string at a specified position. The syntax takes four parameters: the original string, start position (1-based), length to delete, and replacement string.

-- Basic syntax
STUFF(string_expression, start, length, replacement_string)

-- Simple example: Replace characters 7-11 with asterisks
SELECT STUFF('Card: 1234-5678-9012', 7, 4, '****') AS MaskedCard;
-- Result: Card: ****-5678-9012

-- Remove characters by using empty string
SELECT STUFF('ABCDEFGH', 3, 2, '') AS Result;
-- Result: ABEFGH

-- Insert without deletion (length = 0)
SELECT STUFF('SQL Server', 4, 0, 'Awesome ') AS Result;
-- Result: SQLAwesome  Server

The start position is 1-based, meaning the first character is at position 1. Setting length to 0 performs pure insertion without deletion.

Understanding INSERT() in MySQL

MySQL’s INSERT() function serves the same purpose but orders parameters differently. The syntax: original string, start position (1-based), length to replace, and new substring.

-- Basic syntax
INSERT(str, pos, len, newstr)

-- Same masking operation as STUFF() example
SELECT INSERT('Card: 1234-5678-9012', 7, 4, '****') AS MaskedCard;
-- Result: Card: ****-5678-9012

-- Character removal
SELECT INSERT('ABCDEFGH', 3, 2, '') AS Result;
-- Result: ABEFGH

-- Pure insertion (len = 0)
SELECT INSERT('MySQL Database', 7, 0, 'Awesome ') AS Result;
-- Result: MySQL Awesome Database

Despite identical parameter counts and similar positioning, the function names differ. SQL Server uses STUFF() while MySQL uses INSERT(), which can confuse developers working across platforms.

Practical Use Case: SSN Masking

Masking personally identifiable information is a common requirement for data privacy compliance.

-- SQL Server: Mask all but last 4 digits of SSN
DECLARE @SSN VARCHAR(11) = '123-45-6789';

SELECT STUFF(@SSN, 1, 7, 'XXX-XX-') AS MaskedSSN;
-- Result: XXX-XX-6789

-- More flexible approach for varying formats
SELECT 
    SSN,
    STUFF(SSN, 1, LEN(SSN) - 4, REPLICATE('X', LEN(SSN) - 4)) AS MaskedSSN
FROM Employees;

-- MySQL equivalent
SELECT 
    SSN,
    INSERT(SSN, 1, LENGTH(SSN) - 4, REPEAT('X', LENGTH(SSN) - 4)) AS MaskedSSN
FROM Employees;

This approach dynamically calculates how many characters to mask, making it resilient to format variations.

Manipulating Delimited Strings

Both functions shine when modifying comma-separated or delimited lists without parsing into arrays.

-- SQL Server: Replace specific item in CSV list
DECLARE @Tags VARCHAR(100) = 'javascript,python,ruby,java';
DECLARE @StartPos INT = CHARINDEX('ruby', @Tags);

SELECT STUFF(@Tags, @StartPos, 4, 'rust') AS UpdatedTags;
-- Result: javascript,python,rust,java

-- Remove an item from the list
DECLARE @Categories VARCHAR(100) = 'electronics,books,clothing,toys';
DECLARE @RemovePos INT = CHARINDEX('clothing,', @Categories);

SELECT STUFF(@Categories, @RemovePos, 9, '') AS UpdatedCategories;
-- Result: electronics,books,toys

For MySQL:

-- Replace item in delimited list
SET @Tags = 'javascript,python,ruby,java';
SET @StartPos = LOCATE('ruby', @Tags);

SELECT INSERT(@Tags, @StartPos, 4, 'rust') AS UpdatedTags;
-- Result: javascript,python,rust,java

-- Add item at specific position
SET @List = 'apple,banana,date';
SET @InsertPos = LOCATE('banana', @List) + LENGTH('banana');

SELECT INSERT(@List, @InsertPos, 0, ',cherry') AS UpdatedList;
-- Result: apple,banana,cherry,date

Building Dynamic Identifiers

Generate formatted codes or identifiers by inserting segments into templates.

-- SQL Server: Generate product SKUs
SELECT 
    ProductID,
    STUFF(
        STUFF(
            CONCAT('PRD', RIGHT('00000' + CAST(ProductID AS VARCHAR), 5)),
            4, 0, '-'
        ),
        8, 0, '-'
    ) AS SKU
FROM Products
WHERE ProductID IN (1, 42, 999);

-- Results:
-- 1    -> PRD-00-001
-- 42   -> PRD-00-042
-- 999  -> PRD-00-999

Nested STUFF() calls insert hyphens at precise positions to create formatted codes.

Handling NULL Values and Edge Cases

Both functions return NULL if any parameter is NULL, requiring defensive coding.

-- SQL Server: Safe masking with NULL handling
SELECT 
    COALESCE(
        STUFF(PhoneNumber, 1, 6, 'XXX-XX'),
        'No Phone'
    ) AS SafeMasked
FROM Contacts;

-- Validate position bounds
SELECT 
    CASE 
        WHEN LEN(Email) >= 3 THEN 
            STUFF(Email, 2, LEN(Email) - 3, REPLICATE('*', LEN(Email) - 3))
        ELSE Email
    END AS MaskedEmail
FROM Users;

MySQL version:

-- Safe operations with COALESCE
SELECT 
    COALESCE(
        INSERT(PhoneNumber, 1, 6, 'XXX-XX'),
        'No Phone'
    ) AS SafeMasked
FROM Contacts;

-- Boundary validation
SELECT 
    CASE 
        WHEN LENGTH(Email) >= 3 THEN 
            INSERT(Email, 2, LENGTH(Email) - 3, REPEAT('*', LENGTH(Email) - 3))
        ELSE Email
    END AS MaskedEmail
FROM Users;

Performance Considerations

These functions operate on single rows and don’t benefit from indexing. For large datasets, consider alternatives.

-- SQL Server: Inefficient for millions of rows
SELECT STUFF(Description, 1, 10, '...') 
FROM LargeTable; -- Table scan required

-- Better: Computed column for frequently accessed masked data
ALTER TABLE Customers
ADD MaskedSSN AS STUFF(SSN, 1, 7, 'XXX-XX-') PERSISTED;

CREATE INDEX IX_MaskedSSN ON Customers(MaskedSSN);

For MySQL:

-- Generated column approach
ALTER TABLE Customers
ADD MaskedSSN VARCHAR(11) 
GENERATED ALWAYS AS (INSERT(SSN, 1, 7, 'XXX-XX-')) STORED;

CREATE INDEX idx_masked_ssn ON Customers(MaskedSSN);

Persisted/stored computed columns calculate once during write operations rather than every SELECT.

Cross-Platform Migration Strategy

When migrating between SQL Server and MySQL, create compatibility functions.

-- MySQL: Create STUFF() wrapper
DELIMITER $$
CREATE FUNCTION STUFF(str VARCHAR(8000), pos INT, len INT, newstr VARCHAR(8000))
RETURNS VARCHAR(8000)
DETERMINISTIC
BEGIN
    RETURN INSERT(str, pos, len, newstr);
END$$
DELIMITER ;

-- Now STUFF() works in MySQL
SELECT STUFF('Hello World', 7, 5, 'MySQL') AS Result;
-- Result: Hello MySQL

This wrapper allows SQL Server queries using STUFF() to run on MySQL without modification, simplifying migration and maintaining code consistency across environments.

Combining with Pattern Matching

Integrate with PATINDEX() or REGEXP for dynamic positioning.

-- SQL Server: Redact email domains
SELECT 
    Email,
    STUFF(
        Email, 
        CHARINDEX('@', Email) + 1, 
        LEN(Email), 
        'xxxxx.com'
    ) AS RedactedEmail
FROM Users;
-- user@company.com -> user@xxxxx.com

-- MySQL: Same operation
SELECT 
    Email,
    INSERT(
        Email, 
        LOCATE('@', Email) + 1, 
        LENGTH(Email), 
        'xxxxx.com'
    ) AS RedactedEmail
FROM Users;

These functions provide surgical precision for string manipulation without the overhead of splitting strings into arrays or using complex regular expression replacements. They’re essential tools for data masking, formatting, and transformation in production systems.