SQL Server STUFF in Snowflake, Databricks and BigQuery
Snowflake INSERT and Databricks overlay do what STUFF does. BigQuery has no single function; build the result with SUBSTR and CONCAT. STUFF is also used with FOR XML PATH to concatenate rows, which should become a string aggregate instead.
SQL Server function: STUFF(character_expression, start, length, replace_with_expression) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Different name | INSERT(base_expr, pos, len, insert_expr) | Same argument order as STUFF. Returns NULL if any argument is NULL. | Snowflake docs |
| Databricks | Different name | overlay(input PLACING replace FROM pos FOR len) | Also callable as overlay(input, replace, pos, len); note that replace comes before pos. | Databricks docs |
| BigQuery | Rewrite | CONCAT(SUBSTR(s, 1, pos - 1), replacement, SUBSTR(s, pos + len)) | No STUFF or INSERT string function. | BigQuery docs |
Behavior differences to check
- The common pattern STUFF((SELECT ',' + name FROM ... FOR XML PATH('')), 1, 1, '') only removes the leading comma of an XML-built list. Replace the whole expression with LISTAGG (Snowflake), string_agg or listagg (Databricks) or STRING_AGG (BigQuery). See STRING_AGG.
- SQL Server STUFF returns NULL when start is larger than the string length. Check how each target handles positions past the end before relying on it.
Examples
Mask the middle of a phone number
SELECT STUFF(phone, 4, 4, '****') AS masked FROM customers;SELECT INSERT(phone, 4, 4, '****') AS masked FROM customers;SELECT overlay(phone PLACING '****' FROM 4 FOR 4) AS masked FROM customers;SELECT CONCAT(SUBSTR(phone, 1, 3), '****', SUBSTR(phone, 8)) AS masked FROM customers;