Snowflake SQL string functions let you combine, inspect, search, split, extract, and clean text values in queries. Common choices include CONCAT or || for joining strings, SUBSTR and SPLIT_PART for extracting text, TRIM and REPLACE for cleanup, and regular-expression functions for pattern-based work.
How Snowflake handles strings
Snowflake stores ordinary text in VARCHAR values, and most string functions take a string expression and return another string, a number, or an array. You can use them in a SELECT list, a filter, a join condition, or a larger expression. For example, a function can standardize an email address for display, or help compare two values that arrived in different formats.
Many functions accept expressions rather than just literal text, so you can combine fields and calculations. Snowflake can convert some non-string inputs implicitly, but explicit conversion with TO_VARCHAR makes intent clearer when a value is numeric, a date, or a semi-structured value. Format choices matter: converting a date or number to text is not the same as changing the underlying value.
String operations are not automatically language-aware. Case conversion, sorting, and comparisons can depend on the expression’s collation, and default behavior may not match every language’s rules. If locale-specific sorting or case behavior matters, check Snowflake’s collation support rather than assuming that a basic uppercase or lowercase conversion handles every linguistic edge case.
Joining and formatting text
Use CONCAT or the || operator to join strings. Both are useful for building labels, identifiers, or display values. The following query puts a space between two name fields:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
Snowflake’s CONCAT returns NULL if an input argument is NULL. That can surprise you when one name field is missing: instead of a partial name, the whole expression may become null. Use COALESCE when you want to substitute a value for missing text. With first and last names in their respective columns, this example substitutes an empty string for either null:
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name FROM customers;
The result can still contain an extra space if one name is absent, so trim the completed expression when that is undesirable. Choose a replacement that fits the data: converting a missing middle name to an empty string may be sensible, while replacing a missing account identifier with an arbitrary value could cause incorrect matches.
For fixed-width presentation, LPAD and RPAD add characters on the left or right up to a requested length. They can format codes for display, but padding a value is not a safe way to change its meaning or guarantee a unique identifier. If the source is longer than the requested output width, verify the function’s behavior for your intended use before relying on the result.
Measuring, locating, and extracting substrings
LENGTH returns the number of characters in a string. CHAR_LENGTH is also available for character length, while OCTET_LENGTH reports the number of bytes. These measures can differ for non-ASCII text because one visible character may use multiple bytes in UTF-8. A combining mark can also make what a person sees as one displayed character consist of more than one character in the underlying text.
To extract text by position, use SUBSTR (also written SUBSTRING), LEFT, or RIGHT. For instance, LEFT(product_code, 3) returns the first three characters, while RIGHT(product_code, 4) returns the last four. SUBSTR gives more control over the starting position and length; a negative starting position counts from the end of the string.
For a known delimiter, SPLIT_PART is often easier to read than calculating character positions. Its part number is one-based, and negative numbers count from the end. In this example, with email addresses in an email column, the query returns the text after the first at sign:
SELECT SPLIT_PART(email, '@', 2) AS email_domain FROM customers;
That expression is convenient, but it does not validate that the source is a well-formed email address. A missing delimiter, multiple delimiters, or malformed input may produce a value that should not be treated as verified data. POSITION can check whether a substring occurs and returns its one-based position, or zero if it is not found. Pair such checks with extraction when data quality is uncertain.
Changing case and cleaning text
LOWER and UPPER convert text to lowercase and uppercase. INITCAP applies title-style capitalization. These are useful for display or basic normalization, but they do not prove that two values refer to the same person or entity. Names, product labels, and abbreviations may have intentional capitalization that a generic conversion changes.
TRIM removes leading and trailing spaces by default; LTRIM and RTRIM target one side. Trimming does not remove spaces between words. For example, trimming ' North Shore ' removes the outside spaces but leaves the two spaces between “North” and “Shore.” If internal whitespace must be standardized too, use a replacement or regular-expression function suited to the input.
REPLACE performs literal text replacement. It is a good fit when the text to find is known exactly, such as changing a separator or removing a fixed prefix. It is not a regular expression: pattern characters such as a period are treated as ordinary characters. For character-by-character substitutions, TRANSLATE can map characters from one set to another; for more complex rules, use a regex function.
Splitting strings and working with arrays
SPLIT divides a string at a delimiter and returns an array, rather than returning one selected piece as SPLIT_PART does. This is useful when the number of segments varies or when later SQL should work with the resulting collection. Snowflake also provides array functions for inspecting and transforming arrays, so splitting is often one step in a larger workflow.
Choose between the two functions based on the shape of the result you need:
- Use
SPLIT_PART(value, delimiter, n)when you need one known segment in a query result. - Use
SPLIT(value, delimiter)when you need the set of segments as an array for subsequent processing. - Check how the source handles repeated or missing delimiters before using a segment as a trusted key or category.
For example, splitting a comma-separated tags field does not by itself clean spaces around each tag, validate the tags, or guarantee that commas were not part of a tag value. The format may need a proper data model or a defined escaping rule if delimiters can occur inside values.
Searching and pattern matching
For simple searches, use LIKE or ILIKE in a predicate. The percent sign (%) matches any sequence of characters, and the underscore (_) matches one character. ILIKE provides case-insensitive matching. These operators answer whether a value matches a pattern; they do not return a transformed string.
When the pattern is more complex, Snowflake supplies regular-expression functions such as REGEXP_REPLACE, REGEXP_SUBSTR, and REGEXP_INSTR. For example, with telephone text in a phone column, this expression removes characters other than digits:
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS digits_only FROM contacts;
Snowflake’s regular-expression support follows POSIX extended regular-expression conventions, so syntax from a different regex engine may not work exactly as expected. Test patterns against representative inputs, especially for punctuation, Unicode text, and empty values. A pattern that removes formatting characters is not the same as validating a phone number or determining whether it can be dialed.
Nulls, Unicode, and common pitfalls
Before applying a string function, consider whether its input can be NULL. Many functions return null when their input is null, and concatenation specifically propagates a null argument. COALESCE lets you choose a fallback, while NULLIF can turn a particular value into null. Do not replace nulls automatically without deciding what missing text means in the analysis.
Another common misconception is that “character” always means one visible symbol. UTF-8 text can use multiple bytes for a character, and a displayed symbol can be formed from multiple code points. Consequently, length and position operations should be tested with the actual languages and symbols in your data if they are used to truncate names, calculate limits, or create identifiers.
Case normalization and trimming also do not solve every comparison problem. Two strings can look alike while differing in accents, Unicode composition, invisible characters, or punctuation. For sensitive matching, inspect representative raw values and define a deliberate normalization rule; do not assume LOWER(TRIM(value)) makes all equivalent-looking text identical.
Choosing functions and managing query work
Start with the simplest function that expresses the rule. Use literal operations for known delimiters and exact text, and regular expressions only when a genuine pattern is needed. Simpler expressions are generally easier for another analyst to review and less likely to encode assumptions that the source data does not satisfy.
- Joining fields:
CONCATor||, with explicit null handling if needed. - Extracting a fixed section:
SUBSTR,LEFT,RIGHT, orSPLIT_PART. - Cleaning known text:
TRIM,REPLACE, orTRANSLATE. - Matching a simple pattern:
LIKEorILIKE; matching a richer pattern: a regex function.
String functions do not have a separate per-function price, but running transformations uses query compute. Applying a function to a column in a filter may also make it harder for Snowflake to use micro-partition metadata to eliminate data, depending on the expression and table layout. For frequent, large-scale workloads, compare query profiles and consider storing a consistently normalized value during data preparation. Avoid assuming that an expression is a bottleneck without measuring it.
When several queries need the same cleanup rule, centralizing it in a view or an ingestion/transformation step can make behavior consistent. Keep the original value when it is useful for auditability, and document the normalization rule. A cleaned string can be appropriate for searching or grouping, but retaining the source helps explain unexpected matches and supports later changes to the rule.
Frequently Asked Questions
What is the Snowflake function for concatenating strings?
Use CONCAT(value1, value2) or the || operator. Remember that a null argument makes a CONCAT result null; use COALESCE when a fallback is appropriate.
How do I get text before or after a delimiter?
Use SPLIT_PART(value, delimiter, part_number). Part numbers start at one, so the first segment is 1 and the second is 2. Check the source first if the delimiter may be absent or repeated.
How do I remove spaces in Snowflake?TRIM(value) removes leading and trailing spaces, not spaces inside the text. Use REPLACE for a known literal sequence, or a suitable regular expression when you need to normalize a broader whitespace pattern.
What is the difference between LENGTH and OCTET_LENGTH?LENGTH counts characters, while OCTET_LENGTH counts bytes. They can differ for UTF-8 text, so use the measure that matches the actual requirement.
How can I make a string search case-insensitive?
For wildcard pattern matching, use ILIKE instead of LIKE. For exact comparisons or more specialized behavior, consider the relevant collation and test it with your data rather than assuming a pattern operator applies everywhere.
Related
- Split a String in Excel: Separate Text into Columns
- Accounting Trial Balance Worksheet: How It Works
- Google Sheets SPLIT Function: How to Separate Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMIF Criteria Examples for Text, Dates, and NumbersExcel’s SUMIF function adds values when one matching condition is true. With categories in A2:A7 and amounts in…
- Average Value in Excel: Formula, Examples, and TipsTo calculate an average value in Excel, use the AVERAGE function: with the numbers 12, 15, 18, 21,…
- XLOOKUP Example Sheet: Formula, Setup, and TipsAn XLOOKUP example sheet can find a value in one column and return the matching value from another.…
- Filter Excel by Date: Steps, Custom Ranges, and FixesTo filter dates in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- Excel COUNTIFS Not Blank: Multiple Criteria GuideTo count non-blank cells with multiple criteria in Excel, use the COUNTIFS function with the criteria "" (meaning…
- Filter Excel Table by Color: Steps and TroubleshootingYes. In Excel for Microsoft 365 on Windows, open the filter arrow in the table column containing the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.