Snowflake string functions let you combine, inspect, extract, search, and transform text in SQL. For example, use CONCAT(first_name, ' ', last_name) to join name fields, TRIM(city) to remove surrounding whitespace, and SPLIT_PART(path, '/', 2) to extract a delimited field.
What Snowflake string functions do
A string function takes text as input and returns a value, often another string. These functions are useful in queries, views, and data-cleaning steps when source data is inconsistent or stored in a format that is inconvenient for analysis. Snowflake also offers the || operator for concatenation and operators such as LIKE for matching text against a pattern.
String work is not limited to formatting names. It can include normalizing case before a comparison, extracting a code from a longer identifier, removing punctuation from a phone number, or checking whether a value follows a required pattern. The appropriate function depends on whether the task is literal, positional, or pattern-based.
- Literal changes:
REPLACEsubstitutes one exact sequence of characters for another. - Position-based work:
SUBSTRextracts text by position, whilePOSITIONlocates a substring. - Delimited text:
SPLIT_PARTreturns one field from a delimited string. - Pattern-based work:
REGEXP_REPLACEandREGEXP_LIKEuse regular expressions.
Common functions by task
Knowing a few groups of functions makes Snowflake SQL easier to read. Function names are generally descriptive, but choosing a simple function instead of a complicated expression also makes a transformation easier to maintain.
- Change case:
UPPER,LOWER, andINITCAPreturn uppercase, lowercase, or initial-capitalized text. - Measure:
LENGTHreturns the character length of a string. - Trim:
TRIM,LTRIM, andRTRIMremove specified characters from both ends, the left, or the right. - Extract:
SUBSTR(also calledSUBSTRING),LEFT, andRIGHTreturn portions of a string. - Replace:
REPLACEhandles literal text, whileTRANSLATEmaps individual characters from one set to another. - Search:
POSITIONfinds a substring, andLIKEorILIKEchecks a pattern. - Split:
SPLITreturns an array of pieces;SPLIT_PARTreturns one piece as a string. - Format:
LPADandRPADadd padding to the left or right to reach a requested width.
For example, with no table needed, SELECT SUBSTR('Snowflake', 1, 5); returns Snowf. Snowflake string positions are counted from 1 in functions such as SUBSTR and POSITION; POSITION returns 0 when the searched text is not found.
Combining strings and handling NULL values
Use CONCAT or the || operator to join strings. For example, SELECT 'Snow' || 'flake'; returns Snowflake. With columns, CONCAT(first_name, ' ', last_name) places a space between the two values.
A key detail is how missing values behave. In Snowflake, CONCAT returns NULL if any input expression is NULL; concatenation with || also propagates NULL. That may be appropriate when the full value is required, but it can surprise you when a name or address has an optional component. Use COALESCE when you deliberately want a substitute for NULL:
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name
FROM customers;
This expression avoids a NULL result, but it can leave extra spaces if one name part is missing. If that matters, trim the completed value or build the output with explicit conditions. Decide whether NULL means unknown, not supplied, or not applicable before replacing it with an empty string; those meanings are not interchangeable in reporting.
An empty string is also different from NULL in Snowflake. A value of '' has no characters, while NULL represents the absence of a value. Functions may treat those inputs differently, so test both if a cleaning rule must cover both cases.
Extracting and splitting text
Use SUBSTR when a field has a stable character position, such as a fixed-width code. Its basic form is SUBSTR(string, start, length). For example, SELECT SUBSTR('AB-4921', 4, 4); returns 4921. If the format can vary, fixed positions can silently extract the wrong characters; a delimiter or regular expression may be a better choice.
For delimiter-separated values, SPLIT_PART is often more readable. It takes the string, delimiter, and field number:
SELECT SPLIT_PART('north|west|coast', '|', 2) AS region;
The result is west. The delimiter is a literal string, not a regular-expression pattern. Use SPLIT instead when you need the pieces as an array rather than one selected field. This distinction matters when later SQL needs to process every part, instead of simply selecting a known field.
For a simple delimiter in a known format, prefer SPLIT_PART over a regular expression: it makes the intent clear. For irregular text, changing delimiters, or optional sections, regular expressions can be more flexible, though they are harder to maintain. Neither method validates that the original value is well formed; validation is a separate requirement.
Cleaning, normalizing, and changing case
TRIM is a common first step for imported text. With no character argument, it removes whitespace from the beginning and end of a string. LTRIM and RTRIM apply the same idea to one side. Trimming does not remove spaces in the middle, so TRIM(' North Bay ') returns North Bay, not NorthBay.
When you provide characters to trim, Snowflake treats them as characters to remove from the ends, not as one exact phrase to search for. To replace a literal substring anywhere in a value, use REPLACE. For example, REPLACE('AB-12-34', '-', '') returns AB1234. Use TRANSLATE when the job is a character-by-character mapping rather than replacement of a longer sequence.
Case conversion can make comparisons more consistent: LOWER(email) produces a lowercase version, while UPPER(code) produces uppercase text. INITCAP capitalizes words, but it is not a universal name-correction tool; names, abbreviations, and product labels can have unusual capitalization. Preserve the source value if its original spelling matters, and apply a normalized version only for the task that needs it.
Searching text and using regular expressions
For a simple wildcard search, use LIKE. The percent sign (%) matches any sequence of characters, and underscore (_) matches one character. ILIKE performs a case-insensitive pattern match. For instance, product_name ILIKE '%snow%' can match values containing Snow, snow, or another case variation. A wildcard at the beginning is convenient, but can be more expensive to evaluate on large data than a selective prefix or exact comparison.
Use POSITION('flake', product_name) when you need the location of a literal substring rather than a true/false pattern match. Regular expressions are appropriate when a rule has structure, such as removing every character except digits:
SELECT REGEXP_REPLACE('Call 212-555-0100', '[^0-9]', '') AS digits;
The result is 2125550100. Snowflake regular-expression functions use POSIX-style regular expressions. One useful behavior to know is that REGEXP_LIKE treats the pattern as a match for the whole input, effectively anchoring it at both ends. To allow additional characters around a desired pattern, account for them in the expression. Regex is powerful, but for a fixed delimiter or exact substitution, a basic string function is usually clearer.
Choosing the right function and avoiding mistakes
Start by stating the transformation in plain language: exact replacement, extraction by position, split at a delimiter, or match against a rule. Then choose the narrowest function that expresses it. That approach reduces accidental matches and makes the SQL easier for someone else to review.
- Use
REPLACEfor literal text, not for a pattern; useREGEXP_REPLACEwhen the pattern genuinely varies. - Use
SPLIT_PARTfor a predictable delimiter and a known field number; do not assume it validates the number of fields. - Use
TRIMfor leading and trailing whitespace; use another explicit transformation if internal spaces or tabs must change. - Handle NULLs intentionally before concatenating or comparing values.
- Check case rules:
LIKEis case-sensitive, whileILIKEis case-insensitive. - Test examples with punctuation, empty strings, unexpected delimiters, and non-ASCII characters if those can occur in the data.
String length is not the same as the number of bytes used to store text, and a visible symbol can consist of multiple Unicode code points. Consequently, LENGTH is useful for character-oriented checks but should not be mistaken for a display-width or byte-size measurement. If downstream systems impose a byte limit, use the relevant encoding and storage requirements rather than assuming a character count is enough.
Performance and query design
String functions do not carry a separate per-function fee. Snowflake usage is primarily tied to the compute and other services used by a workload, so a more complex expression can matter if it increases warehouse work or query duration. The actual impact depends on data volume, warehouse size, filters, and the query plan; benchmark representative data rather than assuming every string operation is costly.
Applying a function to a table column in a filter, such as WHERE LOWER(email) = 'a@example.com', can make it harder for Snowflake to use micro-partition metadata to narrow the scan than a filter aligned with the stored values. This is not a guarantee that pruning will fail: inspect query behavior and profile for the real workload. If a normalized value is frequently queried, consider maintaining it as a deliberate derived column or normalizing consistently at ingestion. Keep the original when auditability or original formatting is important.
For large transformations, avoid doing the same expensive conversion repeatedly in multiple expressions if a clearer staged query can calculate it once. Also be careful about moving a filter or transformation if it changes which rows qualify. Correct results come first; optimize only after checking the output and measuring the query.
Frequently Asked Questions
What is the Snowflake function for joining strings?
Use CONCAT or the || operator. Both concatenate text, and a NULL input can make the combined result NULL. Use COALESCE when a missing component should be replaced deliberately.
How do I split a string in Snowflake?
Use SPLIT_PART(text, delimiter, field_number) to return one field, or SPLIT(text, delimiter) to return an array of fields. Choose based on whether you need one scalar value or all the pieces.
How do I remove spaces at the start and end of text?
Use TRIM(column_name) to remove surrounding whitespace. It leaves spaces inside the value unchanged. Use LTRIM or RTRIM when only one side should be trimmed.
Is LIKE case-sensitive in Snowflake?
Yes. LIKE is case-sensitive; use ILIKE for a case-insensitive pattern match. For exact comparisons, choose a consistent case-normalization approach if case should not distinguish values.
How can I remove punctuation from a string?
Use REGEXP_REPLACE with a pattern describing the characters to remove. For example, REGEXP_REPLACE(value, '[^0-9]', '') keeps digits only. Confirm that this is appropriate for the data, since it also removes meaningful symbols such as a plus sign or extension marker.
Related
- Excel Goal Seek on Multiple Cells Explained
- JavaScript split() to Array: How It Works and Examples
- Balance of Payments: Meaning, Accounts, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Hide Columns in Excel Based on a Formula AutomaticallyExcel formulas cannot hide columns by themselves. To hide or show columns when a formula’s result changes, put…
- Link Different Sheets in Excel with FormulasTo link a cell on one Excel sheet to a cell on another, enter a reference such as…
- Excel MAX Function for Dates: Find the Latest DateTo find the latest date in Excel, use =MAX(A2:A20), replacing the range with your date cells. Excel stores…
- Excell Battery Canada: How to Find the Right BatteryIf you are searching for an Excell battery in Canada, first check whether the name on the product…
- Capitalize in Excel Without a Formula: 3 Easy MethodsTo capitalize text in Excel without a formula, use Data > Flash Fill: type the capitalization you want…
- Count Unique Text Values in Excel: Formulas & TipsTo count unique, nonblank text values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,ISTEXT(A2:A100)*(A2:A100<>"")))),0) when the values are in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.